CODEWITHSUNDEEP
sqlpostgresql
Альберт Александров
Альберт Александров

Reputation: 901

Group by and fetch column that is not in group by clause

I have (sample) data:

equipment_id | node_id | value (type: jsonb)
------------------------------
1            | 1       | 0.3
1            | 2       | 0.4
2            | 3       | 0.7
2            | 4       | 0.6
2            | 5       | 0.7

And I want to get the rows that has max value within the same equipment_id:

equipment_id | node_id | value
------------------------------
1            | 2       | 0.4
2            | 3       | 0.7
2            | 5       | 0.7

There is query that does what I want but I'm afraid of performance degradation because of casting jsonb to float:

with cte as (
    select
       equipment_id,
       max(value::text::float) as val
    from metrics
    group by equipment_id
)
select cte.equipment_id, m.node_id, cte.val
from cte
join metrics m on cte.equipment_id = m.equipment_id and cte.val = m.value::text::float

How can I avoid casting?

Upvotes: 0

Views: 39

Answers (2)

Fahmi
Fahmi

Reputation: 37473

You can use row_number()

select * from
(
select *, row_number() over(partition by equipment_id order by value::text::float desc) as rn
from tablename
)A where rn=1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269913

Use distinct on:

select distinct on (equipement_id) m.*
from metrics m
order by equipment_id, value desc;

If your value is actually stored as a string, then use:

order by equipment_id, value::numeric desc;

Upvotes: 2

Related Questions