Re: SQL Syntax advice request

[Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>]

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
>