How do I filter a table to show me the most recent date ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to filter a table so I will get the most recent date for each Id
number. I want to do a report that shows the last time all the calipers were
serviced. Thank you.
 
The code below assumes that the table name is called tblCaliper, the
primaryKey is called caliperId and the date column is called dateServiced.
The SQL to find the latest service date for each caliper is

SELECT caliperId, dateServiced
FROM tblCaliper AS T1
WHERE dateServiced IN (
SELECT Max(dateServiced) FROM tblCaliper WHERE caliperId = T1.caliperId)

Hope This Helps
 
(Just want to be a nuisance <g>)

Since the SubQuery / SubSQL returns exactly 1 value, I think the equality
Boolean expression may be slightly more efficient here ...
 
Thank you very much.
Primepixie

Gerald Stanley said:
The code below assumes that the table name is called tblCaliper, the
primaryKey is called caliperId and the date column is called dateServiced.
The SQL to find the latest service date for each caliper is

SELECT caliperId, dateServiced
FROM tblCaliper AS T1
WHERE dateServiced IN (
SELECT Max(dateServiced) FROM tblCaliper WHERE caliperId = T1.caliperId)

Hope This Helps
Click to expand...
 
Back
Top