Prev: Try Catch to Pinpoint Set-Based Error?
Next: Selecting Maximum Values in a Query with joins to other tables
From: scott on 7 Jun 2010 12:42
I'm creating a table that will store which products where viewed also viewed
on a website. Basically it will hold the needed info to produce the
"customers also viewed these products" that you see on sites like amazon.

The below ddl displays an example and returns which product id's where
viewed after viewing product id "1". It sums the total of hits each of the
product id's and sorts from the highest amount of hits to the least.

What I need help on is how to return only the top 5 most hits. How can I
limit my results to filter only the top 5?

In this example the correct top 5 results would be:

prodRefID hits
==================
120 4
101 2
160 2
100 1
103 1


IF object_id('tempdb..#tmpMyTable') IS NOT NULL

BEGIN

DROP TABLE #tmpMyTable

END

BEGIN

CREATE TABLE #tmpMyTable (

ID int identity(1,1) NOT NULL,

prodID int NULL,

prodRefID int NULL

);

END

BEGIN

SET NOCOUNT ON

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '120')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '120')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '120')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '120')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '160')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '160')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '140')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '100')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '101')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '101')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '103')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '105')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('3', '90')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('3', '90')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('3', '91')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('3', '92')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '120')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '120')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '110')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '110')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '110')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '90')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '90')

INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '91')

END

DECLARE @prodID int

SET @prodID = '1'

SELECT h.prodRefID,

(SELECT COUNT(h2.prodRefID) AS hittotal

FROM #tmpMyTable h2

WHERE h2.prodID = @prodID AND h2.prodRefID=h.prodRefID) As hits

FROM #tmpMyTable h

WHERE h.prodID = @prodID

GROUP BY h.prodRefID

ORDER BY hits desc

DROP TABLE #tmpMyTable


From: Q on 7 Jun 2010 14:37
Here is a soluton for SQL Server 2005/2008:

DECLARE @prodID int
SET @prodID = '1';

WITH ProductHits (prodRefID, HitsCount, rn) AS
(
SELECT a.prodRefID, a.HitsCount
, ROW_NUMBER () OVER(ORDER BY a.HitsCount DESC)
FROM
(
SELECT h.prodRefID, COUNT(h.ProdRefID) AS HitsCount
FROM #tmpMyTable h
WHERE h.prodID = @prodID
GROUP BY h.prodRefID
) a
)
select prodRefID, HitsCount from ProductHits
where rn <= 5


"scott" wrote:

> I'm creating a table that will store which products where viewed also viewed
> on a website. Basically it will hold the needed info to produce the
> "customers also viewed these products" that you see on sites like amazon.
>
> The below ddl displays an example and returns which product id's where
> viewed after viewing product id "1". It sums the total of hits each of the
> product id's and sorts from the highest amount of hits to the least.
>
> What I need help on is how to return only the top 5 most hits. How can I
> limit my results to filter only the top 5?
>
> In this example the correct top 5 results would be:
>
> prodRefID hits
> ==================
> 120 4
> 101 2
> 160 2
> 100 1
> 103 1
>
>
> IF object_id('tempdb..#tmpMyTable') IS NOT NULL
>
> BEGIN
>
> DROP TABLE #tmpMyTable
>
> END
>
> BEGIN
>
> CREATE TABLE #tmpMyTable (
>
> ID int identity(1,1) NOT NULL,
>
> prodID int NULL,
>
> prodRefID int NULL
>
> );
>
> END
>
> BEGIN
>
> SET NOCOUNT ON
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '120')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '120')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '120')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '120')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '160')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '160')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '140')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '100')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '101')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '101')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '103')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('1', '105')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('3', '90')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('3', '90')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('3', '91')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('3', '92')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '120')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '120')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '110')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '110')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '110')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '90')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '90')
>
> INSERT INTO #tmpMyTable(prodID, prodRefID) VALUES('4', '91')
>
> END
>
> DECLARE @prodID int
>
> SET @prodID = '1'
>
> SELECT h.prodRefID,
>
> (SELECT COUNT(h2.prodRefID) AS hittotal
>
> FROM #tmpMyTable h2
>
> WHERE h2.prodID = @prodID AND h2.prodRefID=h.prodRefID) As hits
>
> FROM #tmpMyTable h
>
> WHERE h.prodID = @prodID
>
> GROUP BY h.prodRefID
>
> ORDER BY hits desc
>
> DROP TABLE #tmpMyTable
>
>
> .
>
 | 
Pages: 1
Prev: Try Catch to Pinpoint Set-Based Error?
Next: Selecting Maximum Values in a Query with joins to other tables