TOP statement 3

[dtfrancis15]

I want to be able to list the top 10 customers by turnover. How would I use the TOP command in the following statement?

SELECT Sum(NetValue) as NetValue, CustomerName
FROM InvoicedOrders
WHERE Month = May AND Year = 2001

 

[tlbroadbent]

SQL does not have a Top N capability but you can use ordered results and SET ROWCOUNT N to achieve the same thing.

Example:

SET ROWCOUNT 10
SELECT Sum(NetValue) as NetValue, CustomerName
FROM InvoicedOrders
WHERE Month = May AND Year = 2001
Group By CustomerName
Order By NetValue Desc

Group by is required in order to aggregate (sum) the NetValue. Order By places the results in descending order by NetValue. SET ROWCOUNT 10 limits the result set to the 10 highest values. Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[ColinM]

SQL Server does have a TOP command, at least from 7.0.
Just squash it in after select and before first field.

SELECT TOP 10 Sum(NetValue) as NetValue, CustomerName
FROM InvoicedOrders
WHERE Month = May AND Year = 2001
Group By CustomerName
Order By NetValue Desc

Should achieve the same result as the rowcount method.

 

[dtfrancis15]

Thanks to both of you. I am now processing a number of internet based reports for our salesman based on top 10 customers in various markets.

 

[tlbroadbent]

ColinM,

My SQL 7.0 BOL documentation indicates there is no TOP N available. When I atempted to use TOP N in a query, I get a syntax error. We have SQL 7, SP1 installed. Which service pack are you running?
Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[ColinM]

I am using SQL7.0 Service Pack 3
It is briefly mention in BOL
and here:

http://support.microsoft.com/support/SQL/Content/inprodhlp/_select.asp

 

[tlbroadbent]

ColinM,

TOP N is mentioned in SQL BOL but only in the syntax of the "Select Into" clause.

Are you executing the "Select Top N ..." query in SQL Query Analyzer or from another client such as VB, ASP or Access? If you are executing the "select top n" query from one of those clients, then the data interface you use may process the "Top N" clause. Meanwhile, executing directly in SQL server via Query Analyzer produces syntax errors.

That is just a thought. I need to experiment with it to determine the true situation. If you have a "Top N" query that does execute in Query Analyzer could you post it so I can review the complete syntax?

Thanks, Terry

;-) "When I hear somebody sigh, 'life is hard', I am always tempted to ask, 'compared to what'?" - Sydney Harris

 

[ColinM]

Don't know whats going on here!

Try the following query in QA:

use northwind
select top 5 productname, unitprice from products
order by unitprice desc

Heres my output

productname unitprice
---------------------------------------- ---------------------
Côte de Blaye 263.5000
Thüringer Rostbratwurst 123.7900
Mishi Kobe Niku 97.0000
Sir Rodney's Marmalade 81.0000
Carnarvon Tigers 62.5000

(5 row(s) affected)


Good luck tracking this one down

 

[tlbroadbent]

Give me another DUH!

The "TOP N" clause doesn't work unless you include the "Order By" clause which is only logical. Terry

;-) "When I hear somebody sigh, 'life is hard', I am always tempted to ask, 'compared to what'?" - Sydney Harris

 

[ColinM]

It actually works runs without the order by on my machine. No syntax error, although the results will be a load of rubbish!

 

[tlbroadbent]

Ok. Strike that "Order By clause required" idea. The problem was the compatibility level of the database. The database I was testing with was set at Compatibility level 65. When set to 70 "Top N" works.

Interestingly, when my current context was master and I fully qualified the table name with the database.owner prefix, "Top N" also worked. So it wasn't the compatibility level of the database queried, but the level of the current context database. Wow, that coupled with the statement in BOL that TOP N doesn't exist in SQL Server had me confused.

Thanks for the help and information, ColinM.
Terry

;-) "When I hear somebody sigh, 'life is hard', I am always tempted to ask, 'compared to what'?" - Sydney Harris

 

[jjonesal]

I'm rather new to tek-tips, and was looking back at some of the most useful posts (the most stars....) - I must say, some good stuff...I'm really learning a lot!!!!!

I agree w/ ColinM's SELECT:

SELECT TOP 10 Sum(NetValue) as NetValue, CustomerName
FROM InvoicedOrders
WHERE Month = May AND Year = 2001
Group By CustomerName
Order By NetValue Desc

But I think I would include the WITH TIES clause to ensure that if the there's a tie for 10th place (tie on the 10th highest net value) you get both customers that tied for 10th place.... Just something to think about...



J. Jones
jjones@cybrtyme.com