On Tue, Mar 31, 2009 at 7:48 PM, Damian Carey <jamianb@gmail.com> wrote:
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.
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")
select mycol,max(id) from mytable group by mycol;
That should do it, I think.
Sean
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.
