Menu
Based on the table called Course below:
How can I select records 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").
And what if I have two date columns in Course table, which are StartDate & EndDate and I want to show the same based on EndDate only.?
I am using PostgreSQL.
217
Here is the syntax that we can use to get the latest date records in SQL Server. Select column_name, .. From table_name Order By date_column Desc; Now, let's use the given syntax to select the last 10 records from our sample table.
SELECT * FROM product WHERE pdate >= DATEADD(day, -30, getdate()).
1 Answer. ORDER BY id ASC; In the above query, we used subquery with the TOP clause that returns the table with the last 5 records sorted by ID in descending order. Again, we used to order by clause to sort the result-set of the subquery in ascending order by the ID column.
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:
67
SELECT "ID", "Course Name", MAX("Course Date") FROM "Course" GROUP BY "ID", "Course Name" If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
