Query last and 2nd to last record

[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.

 

[Lord Kelvan]

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

 

[KARL DEWEY]

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;