asked on

Filter by last group of people entered.

If you have a group of records that are entered every 2 – 3 weeks and you want to pull up the last 'date' group entered, how would you suggest coding that in SQL? I tried MAX but that gives everyone’s last record. Not the last group. I tried LAST but that doesn’t work in SQL. In the list below this is the result of my MAX code. When all I want is everyone who has a date of 8/20/12 (the last group entered).

SELECT dbo.EmployeeList.EmployeeId, dbo.EmployeeList.FirstName, dbo.EmployeeList.LastName, MAX(dbo.SOGItblRandom.RandomDate) AS Expr1
FROM dbo.EmployeeList INNER JOIN
dbo.SOGItblRandom ON dbo.EmployeeList.EmployeeId = dbo.SOGItblRandom.EmployeeId
GROUP BY dbo.EmployeeList.EmployeeId, dbo.EmployeeList.FirstName, dbo.EmployeeList.LastName
EmpId      Date
350      08/07/12
421      08/10/12
952      08/14/12
1552      08/30/11
1707      08/07/12
2983      08/30/11
3157      08/30/11
3161      08/14/12
3165      08/14/12
3223      08/20/12
3240      08/20/12
3303      08/20/12
3466      08/06/12

Guy Hengel [angelIII / a3]

unless you have a "id" that identifies the "group", you only can rely on the max(date) indeed, this way:

SELECT     el.EmployeeId, el.FirstName, el.LastName
, r.RandomDate
FROM         dbo.EmployeeList el
INNER JOIN dbo.SOGItblRandom r
 ON el.EmployeeId = r.EmployeeId
WHERE r.RandomDate = ( SELECT MAX(x.RandomDate) FROM  dbo.SOGItblRandom x )

Open in new window

BobRosas

ASKER

Thank you so much for your quick reply. The code is working but I only got back one record. I think it's because my "RandomDate" is actually a date/time. Could you tell me how I add the formatting so it's just reading the max date?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BobRosas

ASKER

Yes!
Perfect! Fast and accurate! I maxed out your points! Thank you so much!