Thread: problem query ...

problem query ...

From

John Taylor

Date:

31 January 2002, 14:53:18

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'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

Re: problem query ...

From

John Taylor

Date:

31 January 2002, 15:16:21

On Thursday 31 January 2002 17:02, Tom Lane wrote:
> John Taylor <postgres@jtresponse.co.uk> writes:
> > 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.
>
> There's no simple way to do that in standard SQL.  However you can do
> it easily with SELECT DISTINCT ON, if you don't mind using a nonstandard
> construct.  See the "weather report" example on the SELECT reference
> page.

That was it!
I did try distinct on before, but was trying to be too complicated, with group or subselects.
What I wanted is:

select distinct on (id)  id,name,descr from john order by id,update desc;

Thanks
JohnT

Re: problem query ...

From

Tom Lane

Date:

31 January 2002, 15:16:21

John Taylor <postgres@jtresponse.co.uk> writes:
> 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.

There's no simple way to do that in standard SQL.  However you can do
it easily with SELECT DISTINCT ON, if you don't mind using a nonstandard
construct.  See the "weather report" example on the SELECT reference
page.

            regards, tom lane

Re: problem query ...

From

Frank Bax

Date:

31 January 2002, 15:45:12

select mytable.* from mytable, (select id,max(update) as update from
mytable group by id) maxes where mytable.id = maxes.id and mytable.update =
maxes.update;

At 04:24 PM 1/31/02 +0000, John Taylor 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'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)
>

Re: problem query ...

From

John Taylor

Date:

31 January 2002, 16:05:44

On Thursday 31 January 2002 17:35, Frank Bax wrote:
> select mytable.* from mytable, (select id,max(update) as update from
> mytable group by id) maxes where mytable.id = maxes.id and mytable.update =
> maxes.update;
>

I didn't think of putting the subselect there, I was trying in the where clause.
However, I think using distinct on is probably going to run faster.

Thanks
JohnT

Re: problem query ...

From

Tom Lane

Date:

31 January 2002, 16:49:12

John Taylor <postgres@jtresponse.co.uk> writes:
> On Thursday 31 January 2002 17:35, Frank Bax wrote:
>> select mytable.* from mytable, (select id,max(update) as update from
>> mytable group by id) maxes where mytable.id = maxes.id and mytable.update =
>> maxes.update;

> I didn't think of putting the subselect there, I was trying in the
> where clause.  However, I think using distinct on is probably going to
> run faster.

DISTINCT ON will certainly be quicker.  Another point is that the
behavior isn't necessarily exactly the same.  Suppose that id/update
isn't unique.  Frank's query will give you all the rows with the
maximal update value for each id value.  DISTINCT ON will give you
only one of those rows --- one chosen at random, if you just order
by id and update, or you can order by additional columns to determine
which of the possible rows is selected.  So depending on the behavior
you actually want, either way might be more appropriate.

BTW, although I faulted DISTINCT ON for being nonstandard, subselect
in the FROM clause isn't necessarily portable either; it is standard
but a lot of allegedly-SQL DBMSes don't support it (including Postgres
prior to 7.1).  If you wanted to do this in a way that's actually
portable, you might have to create a temp table, do the select max/group
by id into the temp table, and then join the temp table against the
original.  Yech.

            regards, tom lane

Re: problem query ...

From

Ugly Hippo

Date:

02 February 2002, 06:36:09

--- 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

Re: problem query ...

From

"PG Explorer"

Date:

02 February 2002, 07:16:04

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