Jan
2

T-SQL – selecting top X element for each group

alkampfer on January 2nd, 2009

Today I need to build a query that selects the most expensive three orders made in a date range for each customer. This is the typical query where the ROW_NUMBER() function introduced with Sql Server 2005 can really helps you. Here is a solution in northwind database.

select
    orderId,
    CustomerId,
    Freight from
    (
    select
        ROW_NUMBER() over (partition by CustomerId order by Freight desc) as RowNumber,
        orderId,
    CustomerId,
    Freight
    from
        orders
    ) as innerTable
where RowNumber < 4
order by CustomerId, Freight desc

the ROW_NUMBER creates a column with an incremental number over some order clause and optionally partitioned over some columns. In this example I partition by CustomerId, this means that for each CustomerId the RowNumber starts again with number 1 then I order for “Freight desc”. I put everything in an inner Select and from the outer query I select only the rows whose RowNumber is less than 4, the result is the list of the three order with higher Freight for each customer, really simple ;)

10835    ALFKI    69.53

10692    ALFKI    61.02

10952    ALFKI    40.42

10625    ANATR    43.90

10926    ANATR    39.92

10759    ANATR    11.99

10573    ANTON    84.84

10856    ANTON    58.43

10507    ANTON    47.45

10768    AROUT    146.32

10558    AROUT    72.97

10355    AROUT    41.95

…

…

 

Alk.

kick it on DotNetKicks.com

Uncategorized
Subscribe
Delicious
digg
technorati
google

6 Responses to “T-SQL – selecting top X element for each group”

  1. Dan Waldron
    January 2nd, 2009 at 10:16 am

    You know, I have to tell you, I really enjoy this blog and the insight from everyone who participates. I find it to be refreshing and very informative. I wish there were more blogs like it. Anyway, I felt it was about time I posted, I’ve spent most of my time here just lurking and reading, but today for some reason I just felt compelled to say this.

  2. Andy
    January 9th, 2009 at 5:52 am

    That helps me so much,
    THANK YOU.

  3. EK
    March 20th, 2009 at 6:02 pm

    Wonderful! I had never used the ROW_NUMBER function before. What a great trick!

  4. Luca
    May 7th, 2009 at 6:25 am

    Hi,

    it’s possibile emulate this query with NH HQL or Criteria…

    thx

  5. alkampfer
    May 7th, 2009 at 7:53 am

    Actually with NHibernate doing such a thing is a breeze thank to the SetMaxResult() and SetFirstResult() methods of ICriteria and IQuery. The great advantage is that nhibernate translate it into the appropriate SQL instruction based on choosen dialect.

  6. Paul Els
    August 4th, 2010 at 10:08 am

    Dude – YOU ROCK !!! Thank you SOOOO MUCH !!!