Menu
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
636
Removing duplicate rows from a query result set in PostgreSQL can be done using the SELECT statement with the DISTINCT clause. It keeps one row for each group of duplicates. The DISTINCT clause can be used for a single column or for a list of columns.
The DISTINCT clause can be applied to one or more columns in the select list of the SELECT statement.
Most of the SELECT DISTINCT queries will perform exactly as fast as their simple SELECT counterparts, because the optimizer will do away with the step necessary for eliminating duplicates.
From experiments, I founded that the GROUP BY is 10+ times faster than DISTINCT. They are different. So what I learned is: GROUP-BY is anyway not worse than DISTINCT, and it is better sometimes.
Use order by to get the max index_id
SELECT DISTINCT ON (level) index_id
FROM indexes
order by level, index_id desc
Try tou use GROUP BY instead of DISTINCT ON
SELECT MAX(index_id) FROM indexes GROUP BY level
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
