• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

Compare values from the most recent row to the one just before it

Hi all,

A table has a timestamp t, and some numeric column, n

I need sql statement that would compare n from the row where t is max [max(t)] to:
 the value of n in the row where t is right before max(t)
-- OR --
 the value of n in the row where t is the second before max(t)

To clarify:
t                                           n
2013-01-28 19.00.00           23
2013-01-28 19.00.01           88
2013-01-28 19.00.02           2
2013-01-28 19.00.03           3


I need sql statement that would compare 3 to:
 2
-- OR --
 88

Any help is appreciated
0
darrgyas
Asked:
darrgyas
1 Solution
 
momi_sabagCommented:
You need to generate row numbers and then join to yourself

With a as ( select row_number() over (order by t desc) as row , n from hourtable)
Select t1.n, t2.n
From a t1 join a t2 on t1.rown=t2.rown+1
0
 
darrgyasAuthor Commented:
thank you
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now