Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL - SELECT TOP


T-SQL - SELECT TOP

Author
Message
J-440512
J-440512
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 949
I agree that the first thing I would have done to correct the query would be to make it work by adding the obviously missing GROUP BY clause. AFTER that, if I could not correctly determine that last scenario was the proper answer it would be a simple matter to populate a few tables and run the query.

Owing to the fact that query would not even run, I think that the set of possible answers did not match the situation. Imagine if a fourth alternative "Add GROUP BY" had been presented. By itself it would not have produced the stipulated results but at the very least it would have turned it into a runnable query. How many would have selected the fourth answer ?

I have seen a few more questions of this ilk. For instance where the official solution was to change a table definition, which in real-life would mean that it was likely to break existing code.

When such errors are pointed out, the points awarded for the question of the day should be clawed back.
Ronald H
Ronald H
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1131 Visits: 612
I think the correct query would be to add the GROUP BY of course, and also ORDER BY 3 DESC. Or name the derived item, and order by that name. I wonder how much the author have tested his own query...

Ronald Hensbergen

Help us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/
-------------------------------------------------------------------------
2+2=5 for significant large values of 2
Michael Poppers
Michael Poppers
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 416
Glad 2 C there was discussion 'bout this Q's failings, but I trust most of us picked the A which was closest to what we thought the questioner was after Smile.
ghollis
ghollis
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 127
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.
Balachandra
Balachandra
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2422 Visits: 221
Hi

The Query need the GROUP BY clause also, as we are using SUM an aggregate function.
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 8267
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
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1468 Visits: 3059
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
linna
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 40
I don't understand. Can you give me Full Script ? (use the AventureWorks Database)
I think must add the group by
ghollis
ghollis
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 127
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
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1468 Visits: 3059
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)


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search