Top 5 with Union.

Hot Topic

dynamic query
Value method of xml data type not deterministic, xml column not accessible in 'deleted' table
SSAS 2005 - Server Time Dimension - Cube deployment problem
How to create Merge Replication with latest update always from subscribers
SQL server configuration manager can to other instances of SQL server
A Better Way (Complicated Select Statement)
remote connection to 2005 express: Login Failed?
SQL Server 2000 error code
Pre and Post Exec Event Handler For a Package
Migrate SQL 2000 to New SQL 2005 Installation.

SQL Server

Lookup Transformation: How can I join tables in different databases
selecting colunm with a space ''
WebParts not found for Reporting Services
June CTP - Multi Value Parameter
CASE PROBLEM
Reporting Services 2005 Report Builder
connecting to local server failing
calling stored procedure in VC++
Login failed for user '<<domain>>\<<server>>$'.
Adventure Works DW Script

Top 5 with Union.

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

Answer this question

Top 5 with Union.

  • elpaw

    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
     


  • Alexander Stevenson - MSFT

    Thanks, Umachandar,

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

    Long

  • scheperw

    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.


  • Top 5 with Union.
Answer this question