March 18, 2009 at 2:52 pm
Ok - I am a bit of a SQL Server Newbie but I am hoping someone can help me with a query I am trying to write. I am trying to rank my search results based on the number of items matched in an "IN" clause of a query.
For example
Table
MemberResponses
-----------------
memberID - Int
responseID - Int
memberID responseID
1 3
2 7
2 12
3 1
3 8
3 15
SELECT DISTINCT memberID
FROM memberResponses
WHERE responseID IN (1,3,4,6,8,15)
What I want to do is rank the results based on the number number of matches in the "IN" clause so a memberID that has 3 records (thus matching 3 items in the clause) is ranked higher than one that only matches 1 item in the clause. So in the above example memberID 3 matches 3 times while memberID 1 only matches one so I want to return members (3,1) in that order.
March 18, 2009 at 2:56 pm
Give this a try (no promises)
SELECT memberID
FROM memberResponses
WHERE responseID IN (1,3,4,6,8,15)
GROUP BY memberID
ORDER BY COUNT(*) DESC
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2009 at 10:09 am
if you need to display the number of records try this:
SELECT memberID,
count(*) as occurs
FROM memberResponses
WHERE responseID IN
(
1,3,4,6,8,15
)
GROUP BY memberID
ORDER BY occurs DESC
Hope it helps. Grady Christie
March 30, 2009 at 10:18 am
Or you can use the SQL 2005 new ranking functions...
; WITH Responses
AS
(
SELECTmemberID, COUNT( * ) AS Responses
FROMdbo.memberResponses
WHEREresponseID IN( 1,3,4,6,8,15 )
GROUP BY memberID
)
SELECTROW_NUMBER() OVER( ORDER BY Responses DESC ) AS RowNumber,
RANK() OVER( ORDER BY Responses DESC ) AS MemberRank,
DENSE_RANK() OVER( ORDER BY Responses DESC ) AS MemberDenseRank,
memberID, Responses
FROMResponses
ORDER BY MemberRank
--Ramesh
March 31, 2009 at 11:02 am
Hi
I would go with Ramesh's solution because one it has Count(*) in outer query and second he is using Analytical functions.
Thanks -- Vijaya Kadiyala
March 31, 2009 at 11:50 am
Thank you all for your help. It is most appreciated. - Mark
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply