limiting results returned by a group by clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

limiting results returned by a group by clause

Author

Topic

BazzaM
Starting Member

2 Posts

Posted - 2009-02-12 : 11:35:28

I am trying to limit the results return by a group by clause.

Consider this table

Ref Pet Pet_Name year_born
101 Dog Roxy 2006
101 Dog Max 2006
101 Cat Ava 2007
102 Cat Gremlin 2005
103 Dog buddy 2006
103 Cat Max 1999

I want to return just one entry per ref, and it should be the oldest pet in the house.
In the case of 101, it doesn't matter which one is retuned as 2 dogs are the same age.

The result should look like this:

Ref Pet Pet_Name year_born
101 Dog Roxy 2006
102 Cat Gremlin 2005
103 Cat Max 1999

The query I want to run is something like:

select ref, pet, pet_name, min(year_born)
from my_pets
group by ref

The following query gives me one entry per ref, but
takes min(pet) in this case always "Cat"

select ref, min(pet), min(pet_name), min(year_born)
from my_pets
group by ref

Can I do this without using a sub-query?

Thanks for looking

Bazza

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-12 : 12:00:08

Here are a few ways - not sure if they satisfy your req of no sub-queries though:


declare @t table (Ref int, Pet varchar(10), Pet_Name varchar(15), year_born int)
insert @t
select 101, 'Dog', 'Roxy', 2006 union all
select 101, 'Dog', 'Max', 2006 union all
select 101, 'Cat', 'Ava', 2007 union all
select 102, 'Cat', 'Gremlin', 2005 union all
select 103, 'Dog', 'buddy', 2006 union all
select 103, 'Cat', 'Max', 1999

--using Row_Number()
select ref
       ,pet
       ,pet_name
       ,year_born
from   (
       select ref
              ,pet
              ,pet_name
              ,year_born
              ,row_number() over (partition by ref order by year_born, pet_name desc) rn
       from   @t 
       ) d
where rn = 1


--using CROSS APPLY
select distinct 
       t.ref
       ,ca.pet
       ,ca.pet_name
       ,ca.year_born
from   @t t
cross apply (
       select top 1 
              pet
              ,pet_name
              ,year_born
       from   @t
       where  ref = t.ref
       order by year_born
       ) ca

--using cancatenation
select ref
       ,substring(concat,4+1, 20) pet
       ,substring(concat,4+20+1, 20) pet_name
       ,convert(int, left(concat,4)) year_born
from   (
       select t.ref
              ,min(str(year_born, 4) + convert(char(20), pet) + convert(char(20), pet_name)) concat
       from   @t t
       group by t.ref
       ) d

EDIT (added output):
ref         pet        pet_name        year_born
----------- ---------- --------------- -----------
101         Dog        Roxy            2006
102         Cat        Gremlin         2005
103         Cat        Max             1999

(3 row(s) affected)

ref         pet        pet_name        year_born
----------- ---------- --------------- -----------
101         Dog        Max             2006
102         Cat        Gremlin         2005
103         Cat        Max             1999

(3 row(s) affected)

ref         pet                  pet_name             year_born
----------- -------------------- -------------------- -----------
101         Dog                  Max                  2006
102         Cat                  Gremlin              2005
103         Cat                  Max                  1999

Be One with the Optimizer
TG

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 12:02:24

It will work for this scenerio but maynot work for other case:

Select ref, max(pet), max(pet_name), min(year_born)
from my_pets
group by ref

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 12:05:08

quote:

Originally posted by TG

Here are a few ways - not sure if they satisfy your req of no sub-queries though:


declare @t table (Ref int, Pet varchar(10), Pet_Name varchar(15), year_born int)
insert @t
select 101, 'Dog', 'Roxy', 2006 union all
select 101, 'Dog', 'Max', 2006 union all
select 101, 'Cat', 'Ava', 2007 union all
select 102, 'Cat', 'Gremlin', 2005 union all
select 103, 'Dog', 'buddy', 2006 union all
select 103, 'Cat', 'Max', 1999

--using Row_Number()
select ref
       ,pet
       ,pet_name
       ,year_born
from   (
       select ref
              ,pet
              ,pet_name
              ,year_born
              ,row_number() over (partition by ref order by year_born, pet_name desc) rn
       from   @t 
       ) d
where rn = 1


--using CROSS APPLY
select distinct 
       t.ref
       ,ca.pet
       ,ca.pet_name
       ,ca.year_born
from   @t t
cross apply (
       select top 1 
              pet
              ,pet_name
              ,year_born
       from   @t
       where  ref = t.ref
       order by year_born,pet_name desc
       ) ca

--using cancatenation
select ref
       ,substring(concat,4+1, 20) pet
       ,substring(concat,4+20+1, 20) pet_name
       ,convert(int, left(concat,4)) year_born
from   (
       select t.ref
              ,min(str(year_born, 4) + convert(char(20), pet) + convert(char(20), pet_name)) concat
       from   @t t
       group by t.ref
       ) d

EDIT (added output):
ref         pet        pet_name        year_born
----------- ---------- --------------- -----------
101         Dog        Roxy            2006
102         Cat        Gremlin         2005
103         Cat        Max             1999

(3 row(s) affected)

ref         pet        pet_name        year_born
----------- ---------- --------------- -----------
101         Dog        Max             2006
102         Cat        Gremlin         2005
103         Cat        Max             1999

(3 row(s) affected)

ref         pet                  pet_name             year_born
----------- -------------------- -------------------- -----------
101         Dog                  Max                  2006
102         Cat                  Gremlin              2005
103         Cat                  Max                  1999