From: | Damian Carey <jamianb(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | SQL Syntax advice request |
Date: | 2009-03-31 23:48:07 |
Message-ID: | 2bbc8f530903311648s344aa2d8jf2751cda2d7967a4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2009-04-01 00:22:55 | Re: SQL Syntax advice request |
Previous Message | bijoy franco | 2009-03-31 18:11:33 | OperationalError while using postgresql through python |