Selecting the latest records, GROUP BY

Question

Post reply

Selecting the latest records, GROUP BY

John Costa

Grasshopper

Points: 17
More actions
October 7, 2004 at 8:06 pm

#60535

I have a single table with three columns as follows:

DEPT_ID     DEPT_NAME          DATE_STAMP

-----------------------------------------

11450       Sales              10/01/2001

11450       Sales Divsion      10/30/2002

11450       Sales Dept.        10/30/2003

11550       Computer Dept      11/20/2002

11550       IT Division        12/31/2003

11600       Payroll Dept       06/30/2003

11600       Human Resources    07/15/2004

How can I write a single select statement to bring back the department (and its associated name) with the latest date stamp? My result set should appear as follows:

11450       Sales Dept.        10/30/2003

11550       IT Division        12/31/2003

11600       Human Resources    07/15/2004

Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TriggerMe SSChampion Points: 11752 More actions · Answer 1

TriggerMe

SSChampion

Points: 11752

October 7, 2004 at 9:18 pm

#525874

You need a Corellated SubQuery for this.

Try this,

SELECT A.Dept_ID, A.Dept_Name, A.Date_Stamp

FROM Departments A

WHERE Date_Stamp IN (SELECT MAX(Date_Stamp)

FROM Departments B

WHERE A.DeptI_D = B.Dept_ID)

Kindest Regards,

John Costa Grasshopper Points: 17 More actions · Answer 2

Trigger,

Thanks for your assistance. Your SQL helped greatly. Now to expand on the original question. What if my table is set up as follows:

DEPT_ID DEPT_NAME DATE_STAMP TIME_STAMP

---------------------------------------------------------

11450 Sales 10/01/2001 183220

11450 Sales Division 10/30/2002 196702

11450 Sales Dept. 10/30/2002 197804

11550 Computer Dept 11/20/2002 196456

11550 IT Division 12/31/2003 164543

11600 Payroll Dept 06/30/2003 098736

11600 Human Resources 07/15/2004 076443

Notice I now have two records for department 11450 with the same date stamp (10/30/2002) but different time stamps. How do I modify your original query to pull back the correct result set as follows (latest date stamp AND latest time stamp):

11450 Sales Dept. 10/30/2003

11550 IT Division 12/31/2003

11600 Human Resources 07/15/2004

Thanks again for your help.

TriggerMe SSChampion Points: 11752 More actions · Answer 3

Sorry for taking so long to get back to you, I rarely look at the forums on weekends.

Just use the same query only this time select the TIME_STAMP Column as well like below.

SELECT A.Dept_ID, A.Dept_Name, A.Date_Stamp, A.Time_Stamp

FROM Departments A

WHERE Date_Stamp IN (SELECT MAX(Date_Stamp)

FROM Departments B

WHERE A.DeptI_D = B.Dept_ID)

Kindest Regards,

g-168250 Valued Member Points: 60 More actions · Answer 4

Hi,

Veteran might have overlooked something. Try:

SELECT A.Dept_ID,

A.Dept_Name,

A.Date_Stamp

FROM Departments A

WHERE A.Date_Stamp = (SELECT MAX(Date_Stamp)

FROM Departments B

WHERE A.Dept_ID = B.Dept_ID)

AND

A.Time_Stamp = (SELECT MAX(Time_Stamp)

FROM Departments C

WHERE A.Dept_ID = C.Dept_ID AND

A.Date_Stamp = C.Date_Stamp)