T-SQL - SELECT TOP

Question

T-SQL - SELECT TOP

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

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

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 1

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 More actions · Answer 2

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

selectSalesRank

,SalesPerson

,TotSalesDollars

from Sales

where SalesRank <= 25

linna SSC Journeyman Points: 81 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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: 442467 More actions · Answer 6

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.