Xtremcool Xtremcool - 3 years ago 80
SQL Question

Get the group by on 2 column and date as latest

I have 2 table and I m fetching record as below

enter image description here

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.

Answer Source

You just want MAX(date):

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download