Hi all,
Apologies for the novice SQL syntax question, but I've been going for hours.
Our Postgres based app is much more sophisticated than this question
indicates, but it is a distributed Java desktop app using Hibernate,
so for good or for bad I remain pretty ignorant of a lot of basic SQL.
If anyone could please point me in the right direction, or to a useful
resource I would be most grateful.
In essence, I have a table similar to this ...
id | mycol
============
1001 | 555
1002 | 555
1003 | 556
1004 | 556
1005 | 556
1006 | 558
etc
I just need to find the MAX "id" value for each distinct "mycol" value.
So I want to find "1002,1005,1006". (Corresponding to mycols = "555,556,558")
I can use "SELECT DISTINCT a.mycol FROM mytable a" to get
"555,556,558", but that is only half way.
Of course I can't use
SELECT MAX(a.id) FROM mytable a WHERE a.mycol IN (
SELECT DISTINCT a.mycol FROM mytable a
)
I can of course do this procedurally in Java if needs be ...
SELECT MAX(a.id) FROM mytable a WHERE a.mycol = 555 (then 556, 558)
I would clearly prefer to get the SQL about right and do it server side.
Any assistance is greatly appreciated.
-Damian