TSQL -Review Query

Question

Hi,

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 ?

Thanks,

---

EVA05

Answer 1

Try

;

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


My blog