group by to return correlated results

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

group by to return correlated results

Al Danial
This table keeps track of how far two people ran in
a given week:

create table t(name, day, distance);

insert into  t values("al", "monday"   ,  4.0);
insert into  t values("al", "tuesday"  ,  4.1);
insert into  t values("al", "wednesday",  5.5);
insert into  t values("al", "thursday" ,  2.3);
insert into  t values("al", "friday"   ,  8.1);
insert into  t values("al", "saturday" ,  2.2);
insert into  t values("ed", "thursday" , 14.0);
insert into  t values("ed", "sunday"   , 18.4);

I want to show the name and day that corresponds to
each person's maximum distance.  I know I can use
'group by' like so:

sqlite> select name,max(distance) from t group by name;
name        max(distance)
----------  ----------
al          8.1
ed          18.4

which is fine.  But if I also want to know the day of
the week when the maximum occurred for each person
then things go haywire:

sqlite> select name,day,max(distance) from t group by name;
name        day         max(distance)
----------  ----------  ----------
al          monday      8.1
ed          thursday    18.4

which isn't right because the day shown here isn't the day
when the maximum occurred.  How would I rewrite the query
so that I'd see this result:

name        day         max(distance)
----------  ----------  ----------
al          friday       8.1
ed          sunday     18.4

  ?        -- Al
Reply | Threaded
Open this post in threaded view
|

Re: group by to return correlated results

Darren Duncan
A simple answer is to use a subquery:

   select name, day, distance
   from (
     select max(distance) as max_distance
     from t
     group by name
   ) as foo inner join t on t.distance = foo.max_distance

My exact syntax may be off (eg, the second 'as' may need removing)
but otherwise you should be able to use that.

-- Darren Duncan

At 1:35 AM -0400 6/22/05, Al Danial wrote:

>This table keeps track of how far two people ran in
>a given week:
>
>create table t(name, day, distance);
>
>insert into  t values("al", "monday"   ,  4.0);
>insert into  t values("al", "tuesday"  ,  4.1);
>insert into  t values("al", "wednesday",  5.5);
>insert into  t values("al", "thursday" ,  2.3);
>insert into  t values("al", "friday"   ,  8.1);
>insert into  t values("al", "saturday" ,  2.2);
>insert into  t values("ed", "thursday" , 14.0);
>insert into  t values("ed", "sunday"   , 18.4);
>
>I want to show the name and day that corresponds to
>each person's maximum distance.  I know I can use
>'group by' like so:
>
>sqlite> select name,max(distance) from t group by name;
>name        max(distance)
>----------  ----------
>al          8.1
>ed          18.4
>
>which is fine.  But if I also want to know the day of
>the week when the maximum occurred for each person
>then things go haywire:
>
>sqlite> select name,day,max(distance) from t group by name;
>name        day         max(distance)
>----------  ----------  ----------
>al          monday      8.1
>ed          thursday    18.4
>
>which isn't right because the day shown here isn't the day
>when the maximum occurred.  How would I rewrite the query
>so that I'd see this result:
>
>name        day         max(distance)
>----------  ----------  ----------
>al          friday       8.1
>ed          sunday     18.4
>
>   ?        -- Al

Reply | Threaded
Open this post in threaded view
|

RE: group by to return correlated results

cronos-2
Shouldn't it be:

   select name, day, distance
   from (
     select name as max_name,max(distance) as max_distance
     from t
     group by name
   ) as foo inner join t on t.distance = foo.max_distance and
t.name=foo.max_name

and this still returns two rows when someone has run the same distance on
more than one day - perhaps this is or is not what you want?

-----Original Message-----
From: Darren Duncan [mailto:[hidden email]]
Sent: 22 June 2005 07:14
To: [hidden email]; Al Danial
Subject: Re: [sqlite] group by to return correlated results


A simple answer is to use a subquery:

   select name, day, distance
   from (
     select max(distance) as max_distance
     from t
     group by name
   ) as foo inner join t on t.distance = foo.max_distance

My exact syntax may be off (eg, the second 'as' may need removing)
but otherwise you should be able to use that.

-- Darren Duncan

At 1:35 AM -0400 6/22/05, Al Danial wrote:

>This table keeps track of how far two people ran in
>a given week:
>
>create table t(name, day, distance);
>
>insert into  t values("al", "monday"   ,  4.0);
>insert into  t values("al", "tuesday"  ,  4.1);
>insert into  t values("al", "wednesday",  5.5);
>insert into  t values("al", "thursday" ,  2.3);
>insert into  t values("al", "friday"   ,  8.1);
>insert into  t values("al", "saturday" ,  2.2);
>insert into  t values("ed", "thursday" , 14.0);
>insert into  t values("ed", "sunday"   , 18.4);
>
>I want to show the name and day that corresponds to
>each person's maximum distance.  I know I can use
>'group by' like so:
>
>sqlite> select name,max(distance) from t group by name;
>name        max(distance)
>----------  ----------
>al          8.1
>ed          18.4
>
>which is fine.  But if I also want to know the day of
>the week when the maximum occurred for each person
>then things go haywire:
>
>sqlite> select name,day,max(distance) from t group by name;
>name        day         max(distance)
>----------  ----------  ----------
>al          monday      8.1
>ed          thursday    18.4
>
>which isn't right because the day shown here isn't the day
>when the maximum occurred.  How would I rewrite the query
>so that I'd see this result:
>
>name        day         max(distance)
>----------  ----------  ----------
>al          friday       8.1
>ed          sunday     18.4
>
>   ?        -- Al