Postgres SELECT DISTINCT, only highest values

Question

I am using the following pgsql query to select index_id and making a distinct query on the level column.

SELECT DISTINCT ON (level) index_id FROM indexes

Although its working, it is returning the first index_id for each level, i would like to get the highest index_id in each distinct level

score 14 · Accepted Answer · answered Oct 01 '13 at 12:03

14

Use order by to get the max index_id

SELECT DISTINCT ON (level) index_id 
FROM indexes 
order by level, index_id desc

answered Oct 01 '13 at 12:03

Clodoaldo Neto

When I do this, it doesn't necessarily give me the "last" row of the distinct set. My rows seem almost random - maybe something wrong with my index? – Adam Hughes Aug 31 '20 at 15:29

score 5 · Answer 2 · answered Oct 01 '13 at 12:14

5

Try tou use GROUP BY instead of DISTINCT ON

SELECT MAX(index_id)  FROM indexes GROUP BY level

answered Oct 01 '13 at 12:14

valex

2

What is faster: DISTINCT ON or MAX + GROUP BY? – Andrei Khotko Jan 13 '20 at 09:26
I have heard DISTINCT ON is but dont' have source – Adam Hughes Aug 31 '20 at 15:23

2 Answers2