Trouble with MAX query

[rmitchell]

I have a table where each record is timestamped. I need a query that returns the record with the most recent date/time. The query below works but I need to add more columns and when I do I get more records than I want returned.

SELECT UpdateLiningStatus.MachineNumber, Max(UpdateLiningStatus.TimeStamp) AS MaxOfTimeStamp
FROM UpdateLiningStatus
GROUP BY UpdateLiningStatus.MachineNumber
HAVING (((UpdateLiningStatus.MachineNumber)="CLN33501"));

How can I have the additional columns I need returned and still only return the single, most recent record?

Thanks!

 

[jdraw]

Try a query along this idea (untested)

SELECT fld1,fl2,fld99,timestamp from yourTable
WHERE timestamp =
(Select Max(timestamp) from your table
Where MachineNumber="CLN33501")

 

[ChiOscar]

Try the following code:

SELECT TOP 1 UpdateLiningStatus.MachineNumber, UpdateLiningStatus.TimeStamp
FROM UpdateLiningStatus
WHERE UpdateLiningStatus.MachineNumber="CLN33501"
ORDER BY UpdateLiningStatus.TimeStamp DESC

 

[boblarson]

try a query along this idea (untested)

select fld1,fl2,fld99,timestamp from yourtable
where timestamp =
(select max(timestamp) from your table
where machinenumber="cln33501")

^^^^^^^^^ + 1 ^^^^^^^^^^^^

 

[jdraw]

You liked that Bob?

 

[boblarson]

You liked that Bob?

Totally agreed that would be the way to go.

 

[rmitchell]

Thanks jdraw, worked like a charm.