Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Sean DavisDate: 2009-04-01 00:22:55
Subject: Re: SQL Syntax advice request
Previous:From: bijoy francoDate: 2009-03-31 18:11:33
Subject: OperationalError while using postgresql through python

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group