Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

T-SQL - SELECT TOP Expand / Collapse
Author
Message
Posted Wednesday, March 18, 2009 7:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 14, 2014 12:33 PM
Points: 440, Visits: 929
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.
Post #678401
Posted Wednesday, March 18, 2009 7:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,127, Visits: 603
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
Post #678416
Posted Wednesday, March 18, 2009 8:07 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:58 AM
Points: 642, Visits: 400
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 :).
Post #678498
Posted Wednesday, March 18, 2009 8:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, January 12, 2012 12:05 PM
Points: 308, Visits: 126
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.
Post #678506
Posted Wednesday, March 18, 2009 9:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:09 AM
Points: 2,185, Visits: 192
Hi

The Query need the GROUP BY clause also, as we are using SUM an aggregate function.
Post #678609
Posted Wednesday, March 18, 2009 2:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 1,211, Visits: 6,538
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
Post #678879
Posted Wednesday, March 18, 2009 4:41 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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

Post #678994
Posted Wednesday, March 18, 2009 7:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 16, 2009 11:31 PM
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
Post #679091
Posted Thursday, March 19, 2009 9:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, January 12, 2012 12:05 PM
Points: 308, Visits: 126
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)?
Post #679561
Posted Thursday, March 19, 2009 10:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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)

Post #679658
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse