Getting additional info on the result of a SQL max query

Question

Say I want to do this with SQL (Sybase): Find all fields of the record with the latest timestamp.

One way to write that is like this:

select * from data where timestamp = (select max(timestamp) from data)

This is a bit silly because it causes two queries - first to find the max timestamp, and then to find all the data for that timestamp (assume it's unique, and yes - i do have an index on timestamp). More so it just seems unnecessary because max() has already found the row that I am interested in so looking for it again is wasteful.

Is there a way to directly access fields of the row that max() returns?

Edit: All answers I see are basically clever hacks - I was looking for a syntactic way of doing something like max(field1).field2 to access field2 of the row with max field1

Answer 1

SELECT TOP 1 * from data ORDER BY timestamp DESC

Answer 2

Can you try this

SELECT TOP 1 *
FROm    data
ORDER BY timestamp DESC

Answer 3

You're making assumptions about how Sybase optimizes queries. For all you know, it may do precisely what you want it to do - it may notice both queries are from "data" and that the condition is "where =", and may optimize as you suggest.

I know in the case of SQL Server, it's possible to configure indexes to include fields from the indexed row. Doing a select through such an index leaves those fields available.

Answer 4

This is SQL server, but you'll get the idea.

SELECT TOP(1) * FROM data
ORDER BY timestamp DESC;

Answer 5

No, using an aggregate means that you are automatically grouping, so there isn't a single row to get data from even if the group happens to contain a single row.

You can order by the field and get the first row:

set rowcount 1
select * from data order by timestamp desc

(Note that you shouldn't use select *, but rather specify the fields that you want from the query. That makes the query less sensetive to changes in the database layout.)