|
|
Posted by Teresa Masino on 10/02/48 11:44
Actually, I tried the SQL and I think it does work as desired. You
don't get a pretty label of "Other" for the sum of everything else, but
it works as advertised. I am not familiar with the "WITH TIES" syntax
either, so I was curious and tried it in Northwind:
SELECT T.ProductID, SUM(Quantity)
FROM [Order Details] D1
LEFT JOIN (SELECT TOP 5 WITH TIES ProductID
FROM [Order Details]
GROUP BY ProductID
ORDER BY SUM(Quantity) DESC) AS T
ON D1.ProductID = T.ProductID
GROUP BY T.ProductID
ORDER BY SUM(Quantity) DESC
ORDER BY T.ProductID
The above returned a list of 6 records. 5 with product ID's and the
sum of their quantity, and another row with no value for ProductID and
a sum of the quantity for all of those. If you do
select TOP 5 ProductID, SUM(Quantity)
from [Order Details]
group by ProductID
order by ProductID
you'll see the same 5 records that get returned in the first query.
Pretty slick actually. Anyway, I thought it might help you test it if
you had an example from a table you can get to.
I ran this in Query Analyzer vers 8.00.194 and I'm connecting to a
server version Microsoft SQL Server 2000 - 8.00.760. I didn't
experience the problem you had with the words "WITH TIES" getting
merged into one word.
Hope it helps,
Teresa Masino
Navigation:
[Reply to this message]
|