How to get all results, except one row based on a timestamp?

I have an simple question (?) about SQL. I have come across this problem a few times before and I have always solved it, but I'm looking for a more elegant solution and perhaps a faster solution.

The problem is that I would like to select all rows in a table except the one with the max value in a timestampvalue (in this case this is a summary row but it's not marked as this is any way, and it's not releveant to my result).

I could do something like this:

select * from [table] t
where loggedat < (select max(loggedat) from [table] and somecolumn='somevalue')
and somecolumn='somevalue'

But when working with large ta开发者_C百科bles this seems kind of slow. Any suggestions?

If you don't want to change your DB structure, then your query (or one with a slight variation using <> instead of <) is the way to go.

You could add a column IsSummary bit to the table, and always mark the most recent row as true (and all others false). Then your query would change to:

Select * from [table] where IsSummary = 0 and somecolumn = 'somevalue'

This would sacrifice slower speed on inserts (since an insert would also trigger an update of the IsSummary value) in exchange for faster speed on the select query.

If only you don't mind one tiny (4 byte) extra column, then you might possibly go like this:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY loggedat DESC) AS rownum
  FROM [table] t
  WHERE somecolumn = 'somevalue'
    /* and all the other filters you want */
) s
WHERE rownum > 1

In case you do mind the extra column, you'll just have to list the necessary columns explicitly in the outer SELECT.

It may not be the elegant SQL query you're looking for, but it would be trivial to do it in Java, PHP, etc, after fetching the results. To make it as simple as possible, use ORDER BY timestamp DESC and discard the first row.