获取最新的2列和日期组

I have 2 table and I m fetching record as below

我有2个表，我拿着记录如下

The query is as

查询为

Select   BMC.JCRS_Mem_crs_Code,
         BMC.JCRS_Mem_Description,
         BMC.JCRS_Mem_Date,
         BJCM.JCRS_Mast_Title
From     Bus_MemberCourse BMC
Inner Join Bus_JourneymanCourseMaster BJCM
    On BMC.JCRS_Mem_crs_Code = BJCM.JCRS_Mast_Code
Where    BMC.JCRS_Mem_Completed = 1
         And BMC.JCRS_Mem_mem_ID = 5010
Group By BMC.JCRS_Mem_crs_Code,
         BMC.JCRS_Mem_Description,
         BMC.JCRS_Mem_Date,
         BJCM.JCRS_Mast_Title

But what I want is I need to make group by on Jcrs_mem_crs_code and Jcrs_mem_description and if this 2 column data are common then I want to took the latest date only from this multiple dates. i.e. I want to pick only that row and ignore the other rows.

但我想要的是我需要在Jcrs_mem_crs_code和Jcrs_mem_description上进行分组，如果这2列数据是常见的，那么我想从这多个日期中获取最新日期。即我只想选择该行并忽略其他行。

2 个解决方案

#1

You just want MAX(date):

你只想要MAX（日期）：

Select   BMC.JCRS_Mem_crs_Code,
         BMC.JCRS_Mem_Description,
         MAX(BMC.JCRS_Mem_Date) as JCRS_Mem_Date,
         BJCM.JCRS_Mast_Title
From     Bus_MemberCourse BMC
Inner Join Bus_JourneymanCourseMaster BJCM
    On BMC.JCRS_Mem_crs_Code = BJCM.JCRS_Mast_Code
Where BMC.JCRS_Mem_Completed = 1
And   BMC.JCRS_Mem_mem_ID = 5010
Group By BMC.JCRS_Mem_crs_Code,
         BMC.JCRS_Mem_Description,
         BJCM.JCRS_Mast_Title

#2

You don't need a Group by at all (you aren't doing any aggregation in your query).

您根本不需要Group by（您在查询中没有进行任何聚合）。

This can easily be done with a Row_Number() windowed function with a Partition on those two columns whilst ordering by the date:

这可以通过Row_Number（）窗口函数轻松完成，该函数在这两列上具有分区，同时按日期排序：

;With Cte As
(
    Select   BMC.JCRS_Mem_crs_Code,
             BMC.JCRS_Mem_Description,
             BMC.JCRS_Mem_Date,
             BJCM.JCRS_Mast_Title,
             Row_Number() Over (Partition By BMC.JCRS_Mem_crs_Code,
                                             BMC.JCRS_Mem_Description
                                Order By     BMC.JCRS_Mem_Date Desc) As RN
    From     Bus_MemberCourse BMC
    Inner Join Bus_JourneymanCourseMaster BJCM
        On BMC.JCRS_Mem_crs_Code = BJCM.JCRS_Mast_Code
    Where    BMC.JCRS_Mem_Completed = 1
             And BMC.JCRS_Mem_mem_ID = 5010
)
Select  JCRS_Mem_crs_Code,
        JCRS_Mem_Description,
        JCRS_Mem_Date,
        JCRS_Mast_Title
From    Cte
Where   RN = 1

#1