SELECT SQL Statment

SELECT SQL Statment - Possible?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob - 23 Jul 2008 14:42 GMT

Is it possible to pull the top 5 records for each "group by" item? Consider
the following SQL statement:

SELECT TOP 5 PubID, BooksID, SUM(QtySold) FROM SalesData
GROUP BY PubID, BooksID
ORDER BY PubID, BooksID

Assuming there are only 3 publishers and each has 10 books available, I
would like to know the top 5 books by quantity sold for each publisher. The
statement above only provides the top 5 books for all publishers. How could
this statement be rewritten so that I get the top 5 for each publisher?
Would you have to use a UNION statement, for example:

SELECT TOP 5 PubID, BooksID, SUM(QtySold) FROM SalesData WHERE PubID = 1
GROUP BY PubID, BooksID ORDER BY PubID, BooksID
UNION
SELECT TOP 5 PubID, BooksID, SUM(QtySold) FROM SalesData WHERE PubID = 2
GROUP BY PubID, BooksID ORDER BY PubID, BooksID
UNION
SELECT TOP 5 PubID, BooksID, SUM(QtySold) FROM SalesData WHERE PubID = 3
GROUP BY PubID, BooksID ORDER BY PubID, BooksID

?

Thanks for the help,

Rob

Reply to this Message

Plamen Ratchev - 23 Jul 2008 14:59 GMT

You did not specify what version of SQL Server you have. Here is one method
on SQL Server 2005. You can change ROW_NUMBER to DENSE_RANK if you want to
get ties.

SELECT pubid, booksid, qty
FROM (SELECT pubid, booksid, SUM(qtysold) AS qty,
ROW_NUMBER() OVER(PARTITION BY pubid
ORDER BY SUM(qtysold) DESC) AS rk
FROM SalesData
GROUP BY pubid, booksid) AS R
WHERE rk <= 5
ORDER BY pubid, rk, booksid;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Rob - 23 Jul 2008 15:10 GMT

I have SQL Server 2000... would the syntax of the SQL statement below differ
between the two platforms?

> You did not specify what version of SQL Server you have. Here is one
> method on SQL Server 2005. You can change ROW_NUMBER to DENSE_RANK if you
[quoted text clipped - 13 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Plamen Ratchev - 23 Jul 2008 15:26 GMT

Yes, SQL Server 2000 does not support the ranking functions.

Here is something that will work on SQL Server 2000. Note that on large data
set performance will not be good.

SELECT pubid, booksid, qty
FROM (
SELECT A.pubid, A.booksid, A.qty, COUNT(*) AS rk
FROM (SELECT pubid, booksid, SUM(qtysold) AS qty
FROM SalesData
GROUP BY pubid, booksid) AS A
JOIN (SELECT pubid, booksid, SUM(qtysold) AS qty
FROM SalesData
GROUP BY pubid, booksid) AS B
ON A.pubid = B.pubid
AND (A.qty < B.qty
OR (A.qty = B.qty AND A.booksid <= B.booksid))
GROUP BY A.pubid, A.booksid, A.qty) AS T
WHERE rk <= 5
ORDER BY pubid, rk, booksid;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Roy Harvey (SQL Server MVP) - 23 Jul 2008 15:26 GMT

>I have SQL Server 2000... would the syntax of the SQL statement below differ
>between the two platforms?

Yes they differ. Completely!

SELECT *
FROM (SELECT PubID, BooksID, SUM(QtySold) as TotalSold
FROM SalesData as A
GROUP BY PubID, BooksID) as X
WHERE TotalSold IN
(SELECT TOP 5 SUM(QtySold)
FROM SalesData as B
WHERE B.PubID = X.PubID
GROUP BY BooksID
ORDER BY SUM(QtySold) DESC)

Note that if two or more books are tied for fifth place they will all
be returned.

Roy Harvey
Beacon Falls, CT

Reply to this Message

Rob - 23 Jul 2008 16:06 GMT

Thanks Roy!

Looking at the SQL statement you provided below, it doesn't look like it
includes a "ranking column" like the one for the 2005 SQL Server. Is it
possible to include a "ranking" column on a SQL 2000 platform?

Rob

>>I have SQL Server 2000... would the syntax of the SQL statement below
>>differ
[quoted text clipped - 18 lines]
> Roy Harvey
> Beacon Falls, CT

Reply to this Message

Roy Harvey (SQL Server MVP) - 23 Jul 2008 16:22 GMT

>Looking at the SQL statement you provided below, it doesn't look like it
>includes a "ranking column" like the one for the 2005 SQL Server. Is it
>possible to include a "ranking" column on a SQL 2000 platform?

Not simply.

SELECT X.PubID, X.BooksID, X.TotalSold,
(SELECT COUNT(*)
FROM (SELECT PubID, BooksID, SUM(QtySold) as TotalSold
FROM SalesData as A
GROUP BY PubID, BooksID) as Y
WHERE X.PubID = Y.PubID
AND X.TotalSold >= Y.TotalSold) AS Ranked
FROM (SELECT PubID, BooksID, SUM(QtySold) as TotalSold
FROM SalesData as A
GROUP BY PubID, BooksID) as X
WHERE TotalSold IN
(SELECT TOP 5 SUM(QtySold)
FROM SalesData as B
WHERE B.PubID = X.PubID
GROUP BY BooksID
ORDER BY SUM(QtySold) DESC)
ORDER BY X.PubID, Ranked

Roy Harvey
Beacon Falls, CT

Reply to this Message