如何在T-SQL中只打印一行最新的内容?

-----------------------------------------------
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?

这是最后一行表基于时间(改变列)....条件是最近值之前的时间。我该怎么做呢?

2 个解决方案

#1

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.

有一种更整洁的方法可以做到这一点，这种技术不能很好地概括到第三或第四最近的值。OLAP功能可能会有所帮助。

#2

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

#1