none
Top 5 with Union. RRS feed

  • Question

  • I want to know the top 5 patterns in sales, the report will like this:

    Month       Top1 Top2 Top3 Top4 Top5

    2005-06    A1     A2     A3      A4     A5

    2005-07   B1       B2    B3       B4     B5

    2005-08   C1       C2     C3     C4      C5

    --- --- ---

    What I did is:

      declare @StartDate and @EndDate, set @StartDate and @EndDate

    Delete AAAA (AAAA is a table)

    Begin

    While (@EndDate<somedate)

      Insert into AAAA(sales, Month, Pattern)

      Select TOP 5 sales, Month,pattern from sometables order by sales DESC

    increase @StartDate and @EndDate by a month

    End

    Select * from AAAA

     

    It works fine. My question is: Can I get rid of table AAAA?  Is there a better way that just use Top 5 combine with something say Union? ( I tried Union and failed )

    Thanks in advance,

    Long

    Thursday, June 1, 2006 10:16 PM

Answers

  • You didn't mention the version of SQL Server you are using. On SQL Server 2005 you can do the following:
     
     
    with rnk_t as (
    select sales, Month, ROW_NUMBER() OVER(partition by month order by sales desc) as rnk
    from sometables
    where month between @StartDate and @EndDate
    ),
    rnk_t_5 as (
    select sales, month, rnk
    from rnk_t
    where rnk between 1 and 5
    )
    select pt.month, [1] as top1, [2] as top2, [3] as top3 , [4] as top4, [5] as top5
    from rnk_t_5
    pivot ( min(sales) for rnk_t in ( [1], [2], [3], [4], [5] ) ) as pt
    order by pt.month
     
     
     And I am not sure what the A1, A2... represents in your posts. But you can look in Books Online for more details on PIVOT operator to get the required results. This particular query gives the top 5 sales for each month.
     
     You can do the same in SQL Server 2000 also although it is slightly more difficult.
    Thursday, June 1, 2006 10:41 PM

All replies

  • You didn't mention the version of SQL Server you are using. On SQL Server 2005 you can do the following:
     
     
    with rnk_t as (
    select sales, Month, ROW_NUMBER() OVER(partition by month order by sales desc) as rnk
    from sometables
    where month between @StartDate and @EndDate
    ),
    rnk_t_5 as (
    select sales, month, rnk
    from rnk_t
    where rnk between 1 and 5
    )
    select pt.month, [1] as top1, [2] as top2, [3] as top3 , [4] as top4, [5] as top5
    from rnk_t_5
    pivot ( min(sales) for rnk_t in ( [1], [2], [3], [4], [5] ) ) as pt
    order by pt.month
     
     
     And I am not sure what the A1, A2... represents in your posts. But you can look in Books Online for more details on PIVOT operator to get the required results. This particular query gives the top 5 sales for each month.
     
     You can do the same in SQL Server 2000 also although it is slightly more difficult.
    Thursday, June 1, 2006 10:41 PM
  • Thanks, Umachandar,

    I'm using desk top engine 2000, I'll try the pivot  operator.

    Long

    Thursday, June 1, 2006 11:40 PM
  • Sorry, I didn't clarify my last point completely. SQL Server 2000 doesn't support the pivot operator or the ROW_NUMBER function. So it is not so easy to do in SQL Server 2000. You will have to rewrite the query like below:
     
     
    select t2.month
    , min(case t2.rnk when 1 then t2.sales end) as top1
    , min(case t2.rnk when 2 then t2.sales end) as top2
    , min(case t2.rnk when 3 then t2.sales end) as top3
    , min(case t2.rnk when 4 then t2.sales end) as top4
    , min(case t2.rnk when 5 then t2.sales end) as top5
    from (
    select sales, month, rnk
    from (
    select s1.sales, s1.Month
    , (select count(*) from sometable as s2
    where s2.month between @StartDate and @EndDate
    and s2.month = s1.month
    /* You need to change condition below if the sales value will
    not be unique per month */
    and s2.sales >= s1.sales) as rnk
    from sometables as s1
    where s1.month between @StartDate and @EndDate
    ) as t1
    where rnk between 1 and 5
    ) as t2
    group by t2.month
    order by t2.month
     
    Thursday, June 1, 2006 11:54 PM