T-SQL - SELECT TOP

  • Greg Edwards-268690

    SSC-Insane

    Points: 20564

    ghollis (3/18/2009)


    I agree with the previous comment that even with a group by, top 25, and order by, you are not going to get what the question asked for as the group by would have to group on the salescategory and salesperson; whereas, the question asked for the top 25 sales people, so salescategory would need to be removed.

    I agree. Someone participating in multiple 'salescategories' could actually be the top 'salesperson'

    Greg E

  • john.arnott

    SSChampion

    Points: 11882

    One more "real-world" caveat: there could be a tie for the number 25 spot, so simply using "Top 25" in the select may leave a worthy salesperson off the list. One way to include all who belong would be to use the RANK() function. I like to wrap such in a CTE, but a correlated sub-query or other structure may be just as sound (or better) for your use.

    Declare @StartDt Datetime

    Declare @EndDt Datetime

    Set @StartDt = '20090101'

    Set @EndDt = '20090131'

    ;with

    Sales (SalesRank, SalesPerson, TotSalesDollars)

    as

    (Select SalesRank = Rank() over (order by sum(SalesDollars) Desc)

    ,SalesPerson

    ,sum(SalesDollars)

    from #SalesTbl

    Where SalesDate between @StartDt and @EndDt

    group by SalesPerson) --end of CTE

    select SalesRank

    ,SalesPerson

    ,TotSalesDollars

    from Sales

    where SalesRank <= 25

  • linna

    SSC Journeyman

    Points: 81

    I don't understand. Can you give me Full Script ? (use the AventureWorks Database)

    I think must add the group by

  • ghollis

    SSChasing Mays

    Points: 633

    Wouldn't using Top 25 WITH TIES accomplish the same thing, rather than using the CTE (assuming they are using the group by and order by clauses without the SalesCategory being included in the query)?

  • john.arnott

    SSChampion

    Points: 11882

    ghollis (3/19/2009)


    Wouldn't using Top 25 WITH TIES accomplish the same thing, rather than using the CTE (assuming they are using the group by and order by clauses without the SalesCategory being included in the query)?

    Yes, it does. Thank you for pointing out this option. This works well. Here's a test script ( I didn't bother including salesCategory in the table definition).....

    create table #SalesTbl(

    Salesperson char(30)

    ,SalesDollars money

    ,SalesDate datetime

    )

    insert #SalesTbl

    Select 'Judy', 14554.22, '20090103' union All

    Select 'Judy', 15854.22, '20090103' union All

    Select 'Judy', 16554.22, '20090103' union All

    Select 'Joyce', 26554.22, '20090103' union All

    Select 'Ed', 25554.22, '20090103' union All

    Select 'Andy', 24554.22, '20090103' union All

    Select 'Len', 22222.22, '20090103' union All

    Select 'Tom', 22222.22, '20090103' union All

    Select 'Maria', 4554.22, '20090103' union All

    Select 'Chris', 6654.22, '20090103' union All

    Select 'Tony', 4554.22, '20090103' union All

    Select 'Sally', 4654.22, '20090103' union All

    Select 'Bob', 4554.22, '20090103' union All

    Select 'Bob', 4554.22, '20090103'

    Declare @StartDt Datetime

    Declare @EndDt Datetime

    Set @StartDt = '20090101'

    Set @EndDt = '20090131'

    Select top 5 with ties

    Salesperson

    ,sum(salesDollars) TotSales

    from #SalesTbl

    Where SalesDate between @StartDt and @EndDt

    group by SalesPerson

    order by sum(salesDollars) Desc

    and results:

    Salesperson TotSales

    ------------------------------ ---------------------

    Judy 46962.66

    Joyce 26554.22

    Ed 25554.22

    Andy 24554.22

    Len 22222.22

    Tom 22222.22

    (6 row(s) affected)

  • Lynn Pettis

    SSC Guru

    Points: 442144

    Yes, there are issues with the query itself, but what did the question ask? How do you ensure you get the top 25 in sales. The only answer that works there is that you also need to add an ORDER BY SUM(salesdollars) DESC.

Viewing 6 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply