November 6, 2008 at 11:55 am
I have a script that gives the following:
DayID Hits Title
4112 20 Gone with the wind
4112 14 Jaws
4111 10 Star Wars
4001 15 Dumb and Dumber
3999 7 Jaws
The DayID is an integer for the day of the week. I have the script showing what I want but I need to hide dupes (jaws) from my query result based on Title being the same and I only want to show the one with the highest number of hits.
So in this case Only (4112 14 Jaws) would show and the 3999 would not come up in the query. My query is using a join from two tables. How do you compare a single field in SQL? and base which one comes up on a different Integer field?
November 6, 2008 at 12:31 pm
This can easily be done with a CTE and RANK() function. Something like the following example:
DECLARE @Temp AS TABLE (
DayId INT,
Hits INT,
Title VARCHAR(20)
)
INSERT INTO @Temp
VALUES (4112, 20, 'Gone with the wind')
INSERT INTO @Temp
VALUES (4112, 14, 'Jaws')
INSERT INTO @Temp
VALUES (4111, 10, 'Star Wars')
INSERT INTO @Temp
VALUES (4001, 15, 'Dumb and Dumber')
INSERT INTO @Temp
VALUES (3999, 7, 'Jaws');
WITH RankList
AS (
SELECT
RANK() OVER (PARTITION BY T.Title ORDER BY T.Hits DESC) AS [Rank],
DayId,
Hits,
Title
FROM @Temp T
)
SELECT *
FROM RankList
WHERE [Rank] = 1
November 6, 2008 at 12:59 pm
Thanks dmc that worked perfect. Would you know how I could limit the rank just to the first 10? Like just use the Rank on the first ten that I pull. Thanks.
November 6, 2008 at 2:44 pm
Can you explain what you mean by the first 10? With out knowing your data or what you mean by the first 10 records I would limit the SELECT code that has the RANK() to return only the records you want to rank and then you will get the top 1 you want.
If you have a better explanation I or someone might be able to provide additional code sample.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply