SQL Syntax advice request

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

Responses

Browse pgsql-novice by date

  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