From: | Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> |
---|---|
To: | Damian Carey <jamianb(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: SQL Syntax advice request |
Date: | 2009-04-01 00:22:55 |
Message-ID: | 264855a00903311722x1ed929ebj90a78310085f388@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Tue, Mar 31, 2009 at 7:48 PM, Damian Carey <jamianb(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jeffrey Lake - Admin | 2009-04-01 05:10:31 | No REPLACE function ?! |
Previous Message | Damian Carey | 2009-03-31 23:48:07 | SQL Syntax advice request |