Tuesday, November 20, 2012 1:04 AM
I have a table #schoolreview. The table has reviewid as a primary key. The table contains schools referred to a student.
CREATE TABLE #schoolreview ( reviewid INT PRIMARY KEY, studentid INT, referralid INT, eventdate DATETIME, schoolid1 INT, schoolname1 VARCHAR(200), schoolid2 INT, schoolname2 VARCHAR(200)) INSERT #schoolreview VALUES (1, 400, 34232, '2012-11-19', 159, 'AMGT', 160, 'TRYT') INSERT #schoolreview VALUES (2, 400, 34233, '2012-11-19', 145, 'RTP', null, null) INSERT #schoolreview VALUES (3, 401, 34234, '2012-11-19', 560, 'GT', 160, 'TRYT') INSERT #schoolreview VALUES (4, 401, 34235, '2012-11-19', 45, 'TP', null, null) INSERT #schoolreview VALUES (5, 401, 34236, '2012-11-18', 458, 'TPTR', null, null) SELECT * FROM #schoolreview
I want to write a query in such a way that for a given studentid if the eventdate is the same for the different review id ( in the case above- review id-1,2 for studentid-400) pick the row with the maixum schoolids- pick reviewid -1 as it has two schools referrals
but if the eventdate is different pick both the rows
for studentid-401 we would need to pick reviewid-3 and 5 ( 3 because for the date 2012-11-19 he has 2 schoolids referred and reviewid 5 beacuse of a different date)
Please guide as to how we can query the table ?
Tuesday, November 20, 2012 1:21 AMModerator
; WITH cte AS ( SELECT * ,ROW_NUMBER() OVER ( PARTITION BY R.StudentId ,R.EventDate ORDER BY R.SchoolId1 DESC ,R.schoolId2 DESC ) AS Rn FROM #schoolreview R ) SELECT * FROM cte WHERE Rn = 1
For every expert, there is an equal and opposite expert. - Becker's Law
- Proposed As Answer by Naarasimha Tuesday, November 20, 2012 2:39 AM
- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Tuesday, November 27, 2012 9:19 PM