Re: SQL Syntax advice request - Mailing list pgsql-novice

From Sean Davis
Subject Re: SQL Syntax advice request
Date
Msg-id 264855a00903311722x1ed929ebj90a78310085f388@mail.gmail.com
Whole thread Raw
In response to SQL Syntax advice request  (Damian Carey <jamianb@gmail.com>)
List pgsql-novice


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.

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")

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.

Any assistance is greatly appreciated.

-Damian

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

pgsql-novice by date:

Previous
From: Damian Carey
Date:
Subject: SQL Syntax advice request
Next
From: "Jeffrey Lake - Admin"
Date:
Subject: No REPLACE function ?!