Only get newest key-value pairs of owner in select statement

Question

I get a list of values like the following:

Name	Version	Value	Key
N1	1.0	1	K1
N1	1.0	1	K2
N1	1.2	2	K1
N1	1.2	1	K2
N2	1.0	0	K1
N2	1.0	0	K2

I just need the newest (highest version) key-value pairs of every name.

The result would be in this case:

Name	Version	Value	Key
N1	1.2	2	K1
N1	1.2	1	K2
N2	1.0	0	K1
N2	1.0	0	K2

This is how the statement currently looks like.

I already had to split the results into single key-value pairs.

The reason is, I'm using them in Zoho-Analytics for generating graphs.

SELECT inside.* FROM 
(SELECT
         "Name",
         Version,
         to_integer(k1) as Value,
         'k1' as key
FROM  "RD" 
UNION
SELECT
         "Name",
         Version,
         to_integer("k2") as value,
         'k2' as key
FROM  "RD"   order by Version, "Name" asc
) as inside

The ORDER BY inside the subquery makes no sense. (Why a subquery? It's not needed.) — jarlh, Oct 27 '22 at 13:34
`to_integer` is not an ANSI SQL function, so you can remove that tag. — jarlh, Oct 27 '22 at 13:35

Stefanov.sm · Answer 1 · 2022-10-27T13:56:40.843

1

Use distinct on with descending order by version to pick the highest one.

select distinct on (name, key) *
from the_table
order by name, key, version desc;

DB fiddle

edited Oct 27 '22 at 13:56

answered Oct 27 '22 at 13:41

Stefanov.sm

11,215
2
21
21

Only get newest key-value pairs of owner in select statement

1 Answers1