CODEWITHSUNDEEP
sqldatabasepostgresqlgreatest-n-per-group
Aan
Aan

Reputation: 12920

Select rows based on last date

I have a table named Course in a Postgres database:

sample data for table Course

How can I select rows which have course name with latest date? I mean if I have two same course names for one ID, I should only show the latest one as the below result.

Simply, I want only to show the latest row per ("ID", "Course Name").

desired result

And what if I have two date columns in table Course, which are StartDate & EndDate and I want to show the same based on EndDate only?

Upvotes: 39

Views: 69079

Answers (5)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659257

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;

There are faster query techniques for many rows per group. Further reading:

Upvotes: 51

Minoru
Minoru

Reputation: 1730

Try this:

SELECT DISTINCT ON (c."Id", c."Course Name") 
    c."Id", c."Course Name", c."Course Date" 
FROM (SELECT * FROM "Course" ORDER BY "Course Date" DESC) c;

Upvotes: 0

GVIrish
GVIrish

Reputation: 361

SELECT *
FROM (SELECT ID, CourseName, CourseDate, 
      MAX(CourseDate) OVER (PARTITION BY COURSENAME) as MaxCourseDate
FROM Course) x
WHERE 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.

Upvotes: 7

Dev
Dev

Reputation: 3580

SELECT * 
FROM  course
GROUP BY id,course name
order by course_date desc

Upvotes: -1

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26749

SELECT "ID", "Course Name", MAX("Course Date") FROM "Course" GROUP BY "ID", "Course Name"

Upvotes: 10

Related Questions