根据最后日期选择记录

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.

如何选择最新日期的课程名称的记录?我的意思是，如果我有两个相同的课程名称作为一个ID，我应该只显示最新的一个作为下面的结果。

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

简单地说，我只想显示最近的行(“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.?

如果我在课程表中有两个日期列，它们是StartDate & EndDate，我想仅基于EndDate显示相同的内容。

I am using PostgreSQL.

我使用PostgreSQL。

5 个解决方案

#1

In PostgreSQL, to get unique rows for a defined set of columns, the preferable technique is generally DISTINCT ON:

在PostgreSQL中，要为一组已定义的列获取唯一的行，最好的技术通常是:

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.

通过这种方式，您可以得到每一个ID对应的一行——其中包含最新已知的“课程日期”和第一个“课程名称”(根据排序顺序)，以备日期上的联系。

You can drop NULLS LAST if your column is defined NOT NULL.

如果列定义为非空，则可以将NULLS放在最后。

To get unique rows per ("ID", "Course Name"):

要获得每个(“ID”、“课程名称”)的唯一行:

SELECT DISTINCT ON ("ID", "Course Name") *
FROM   "Course"
ORDER  BY "ID", "Course Name", "Course Date" DESC NULLS LAST;

Details in this related answer:

有关答案的详情如下:

• Select first row in each GROUP BY group?
• 在每个组中按组选择第一行?

#2

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

#3

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.

这里的MAX() / (PARTITION BY)允许您在派生表中查找每个课程(分区)的最高课程表。然后，您可以选择课程日期等于该课程的最大课程日期的行。

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.

这种方法的好处是不使用GROUP BY子句，这将限制可以返回哪些列，因为SELECT子句中的任何非聚集列也必须位于GROUP BY子句中。

#4

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;

#5

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