Query last and 2nd to last record

  • Thread starter Thread starter JCricket
  • Start date Start date
J

JCricket

If trying to figure out how to make a query that will look through an job
tracking log and return just the most recent record and the previous record
for a given job ID. For example, when a job ticket is created for a given
problem, it is given a job ID. Technicians will provide updates in the
database, and the time they enter their updates are logged. I just want to
make a query so that if I search for all the jobs worked on today, will
return both the most recent update and the update that was put in just before
that for each job.
 
SELECT yourtable.jobid, yourtable.updatedate
FROM yourtable
WHERE (((yourtable.logid) In (
select top 2 subyourtable.logid
from yourtable as subyourtable
where subyourtable.jobid = yourtable.jobid
order by updatedate desc)))
ORDER BY yourtable.jobid, yourtable.updatedate DESC;

for this query to work you need job id to be the same and logid to be
different and of course the updatedate/time to see the order of the
values

hope this helps

Regards
Kelvan
 
Try this --
SELECT Q.ID, Q.JobDate, Q.Comment
FROM JobTrack AS Q
WHERE ((((SELECT COUNT(*) FROM JobTrack Q1
WHERE Q1.[ID] = Q.[ID]
AND Q1.[JobDate] > Q.[JobDate])+1)<=2))
ORDER BY Q.ID, Q.JobDate DESC;
 
Back
Top