Re: problem query ...

TRY

SELECT * from mytable where name =(
select max(name) from mytable)
Union
SELECT * from mytable where descrip =(
select max(descrip) from mytable)
Union
SELECT * from mytable where update =(
select max(update) from mytable)


> > I want to return 1 row for each id, that contains
> > the maximum update value for that id, and
> > the values for name and desc.
To return only ONE ROW from each the fields name,descrip and update must be
UNIQUE.

PS
desc is a reserved word for descending.

Hope this helps

http://www.pgexplorer.com
GUI tool for postgres




----- Original Message -----
From: "Ugly Hippo" <ugly_hippo@yahoo.ca>
To: "John Taylor" <postgres@jtresponse.co.uk>; <pgsql-novice@postgresql.org>
Sent: Saturday, February 02, 2002 10:34 AM
Subject: Re: [NOVICE] problem query ...


>
> --- John Taylor <postgres@jtresponse.co.uk> wrote:
> >
> > Hi,
> >
> > I hope someone can help with this query, which is
> > causing me great problems.
> >
> > I have a table:
> > create table mytable ( id varchar(10), name
> > varchar(10), desc varchar(10), update integer);
> >
> > I want to return 1 row for each id, that contains
> > the maximum update value for that id, and
> > the values for name and desc.
>
> I would try (I can't connect to my db at the moment):
>
> select id, max(name), max(desc), max(update)
> from mytable
> group by id;
>
> HTH,
> Troy
> ugly_hippo@yahoo.ca
>
> >
> > I've tried everything I can think of, but no luck
> > :-(
> > I'm sure there is some simple trick that I'm
> > missing.
> >
> > Can someone please put me out of my misery !
> >
> > Thanks
> > JohnT
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> > unregister command
> >     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>
> ______________________________________________________________________
> Web-hosting solutions for home and business! http://website.yahoo.ca
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster