Select records based on last date
In PostgreSQL, to get unique rows for a defined set of columns, the preferable technique is generally DISTINCT ON
:
SELECT DISTINCT ON ("ID") *FROM "Course"ORDER BY "ID", "Course Date" DESC NULLS LAST, "Course Name";
Assuming you actually use those unfortunate upper case identifiers with spaces.
You get exactly one row per ID
this way - the one with the latest known "Course Date"
and the first "Course Name"
(according to sort order) in case of ties on the date.
You can drop NULLS LAST
if your column is defined NOT NULL
.
To get unique rows per ("ID", "Course Name")
:
SELECT DISTINCT ON ("ID", "Course Name") *FROM "Course"ORDER BY "ID", "Course Name", "Course Date" DESC NULLS LAST;
Details in this related answer:
SELECT "ID", "Course Name", MAX("Course Date") FROM "Course" GROUP BY "ID", "Course Name"
SELECT *FROM (SELECT ID, CourseName, CourseDate, MAX(CourseDate) OVER (PARTITION BY COURSENAME) as MaxCourseDateFROM Course) xWHERE CourseDate = MaxCourseDate
Here the MAX() OVER(PARTITION BY) allows you to find the highest CourseDate for each Course (the partition) in a derived table. Then you can just select for the rows where the CourseDate is equal to the maximum Coursedate found for that Course.
This approach has the benefit of not using a GROUP BY clause, which would restrict which columns you could return since any non-aggregrate column in the SELECT clause would also have to be in the GROUP BY clause.