Select a subset of a group by

• stephane3d

  Ten Centuries

  Points: 1129

  More actions

  February 12, 2008 at 1:35 pm

  #182895

  Hi,

  Let's say I have a table with 2 colums (date and name). If I want to know how many name per date, I'd do a group by like this:

  select date, name, count(*) as total from tbl_logs group by date, name

  So I'd get:

  Date Name Count

  Feb-12 AAA 150

  Feb-12 BBB 100

  Feb-12 CCC 60

  Feb-12 DDD 50

  Feb-12 EEE 5

  Feb-10 AAA 150

  Feb-10 BBB 100

  Feb-10 CCC 60

  Feb-10 DDD 50

  Feb-10 EEE 5

  Now, let's say I'd like to get only the top 3 rows for each date, so I'd get

  DateNameCount

  Feb-12 AAA150

  Feb-12 BBB100

  Feb-12 CCC60

  Feb-10 AAA150

  Feb-10 BBB100

  Feb-10 CCC60

  How would I do that? Any idea of how to limit the number of groups returned for a date?

  thanks

  Stephane

• Roy Ernest

  SSC-Dedicated

  Points: 38780

  More actions

  February 12, 2008 at 1:41 pm

  #777542

  You can use row_number() over partition

  select * from (select *, row_number() over (partition by Date order by Count desc) as num from ) as z

  where num <= 3

  -Roy

• stephane3d

  Ten Centuries

  Points: 1129

  More actions

  February 12, 2008 at 1:51 pm

  #777551

  It works!

  Thanks a lot

  Stephane

• Roy Ernest

  SSC-Dedicated

  Points: 38780

  More actions

  February 12, 2008 at 1:52 pm

  #777554

  Glad that it worked for you. I got this info from one of the other Forum members.

  -Roy