MS SQL Azure – SQL to Select Distinct list of Child Records based on a Maximum or Minimum Child Field Value

Following on from the previous post I wanted to know the syntax for SQL to do the same but in SQL Azure.

SELECT * FROM dbo.T02AttendanceGame T1    
WHERE Attendance = (
   SELECT max(Attendance)
   FROM dbo.T02AttendanceGame T2
   WHERE T1.FKID=T2.FKID
);

I created the same tables that I created for the MS Access example with the same field names but within a SQL Azure database.

Here’s the same SQL but then creating a view called rather unimaginatively ‘View01’

CREATE VIEW VIEW01 AS SELECT * FROM dbo.T02AttendanceGame T1    
WHERE Attendance = (
   SELECT max(Attendance)
   FROM dbo.T02AttendanceGame T2
   WHERE T1.FKID=T2.FKID
);