Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL Syntax advice request

SQL Syntax advice request

From
Damian Carey
Date:
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

Re: SQL Syntax advice request

From
Sean Davis
Date:


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