2
-----------------------------------------------
Value     ISRepeat    Changed
------------------------------------------------
666-66-8888 NULL    2011-10-19 15:28:47.310
555-55-6666 NULL    2011-10-19 15:27:26.413
444-44-5555 NULL    2011-10-19 15:25:02.660
333-33-4444 NULL    2011-10-19 15:23:57.120
222-22-3333 NULL    2011-10-19 15:13:57.903
222-22-2222 NULL    2011-10-19 15:13:03.517
100-10-2222 NULL    2011-10-19 15:07:52.010
100-10-1111 NULL    2011-10-19 15:06:59.690

I have a table like this. I want to print the value

555-55-6666 NULL    2011-10-19 15:27:26.413

which is the last but one row in table based on TIME(Changed column).... condition is time previous to most recent value. How can I do this?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Rocky111
  • 255
  • 1
  • 5
  • 19
  • possible duplicate of [Row Offset in SQL Server](http://stackoverflow.com/questions/187998/row-offset-in-sql-server) – Phil Oct 20 '11 at 03:13

2 Answers2

3

The most recent time is found by:

SELECT MAX(Changed) AS most_recent FROM AnonymousTable

You want the row with the maximum time less than this:

SELECT MAX(Changed) AS second_latest
  FROM AnonymousTable
 WHERE Changed < (SELECT MAX(Changed) AS most_recent FROM AnonymousTable)

So, you select the relevant row:

SELECT *
  FROM AnonymousTable
 WHERE Changed =
       (SELECT MAX(Changed) AS second_latest
          FROM AnonymousTable
         WHERE Changed < (SELECT MAX(Changed) AS most_recent FROM AnonymousTable)
       )

There may be a neater way to do this, and this technique does not generalize well to the third or fourth most recent value. It is quite possible that OLAP functionality may help.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
1

Try this:

select top 1 *
from YourTable
where Changed not in
(
    select max(Changed)
    from YourTable
)
order by Changed desc

Or:

select *
from
(
    select ROW_NUMBER() over (order by Changed desc) as row_num,
        *
    from YourTable
) a
where row_num = 2
  • @Rocky111 how so? What was the output when you tried them? They worked on my machine. –  Oct 20 '11 at 05:00