• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

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
0
BobRosas
Asked:
BobRosas
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
BobRosasAuthor Commented:
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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:

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 convert(datetime, convert(varchar(10), MAX(x.RandomDate) , 120), 120) FROM  dbo.SOGItblRandom x )
                                            

Open in new window

0
 
BobRosasAuthor Commented:
Yes!
Perfect!  Fast and accurate!  I maxed out your points!  Thank you so much!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now