LougaLo
asked on
SQL query to show previous row and current row values
i got some helps here a couple of days ago about how to get aSQL query to compare the previous row and current values.
Now, I need to display the previous row and current row.
attached is the code and the example table. thanks
here is the desired output
pid MDATE MHEIGHT Serial FLAG
-------------------------- ---------- ---------- ---------- ---------- ---------
1 13-Jul-10 62.5 4
1 7-Aug-10 60 5 FLAG
4 21-Apr-10 57.5 23
4 23-May-10 55.9 24 FLAG
5 21-Apr-10 69.2 31
5 19-May-10 58.6 32 FLAG
prev-current-row.xlsx
Now, I need to display the previous row and current row.
attached is the code and the example table. thanks
here is the desired output
pid MDATE MHEIGHT Serial FLAG
--------------------------
1 13-Jul-10 62.5 4
1 7-Aug-10 60 5 FLAG
4 21-Apr-10 57.5 23
4 23-May-10 55.9 24 FLAG
5 21-Apr-10 69.2 31
5 19-May-10 58.6 32 FLAG
prev-current-row.xlsx
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY pid, MDATE ) Serial FROM #table
)
SELECT x.*, CASE WHEN y.pid IS NOT NULL THEN 'FLAG' ELSE '' END FLAG FROM CTE x
LEFT JOIN
(
SELECT a.*
FROM CTE a
LEFT JOIN CTE b ON a.Serial - 1 = b.Serial AND a.pid = b.pid
WHERE b.MHEIGHT > a.MHEIGHT
) y ON x.pid = y.pid AND x.MDATE = y.MDATE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
JoeNuvo: thank you so much. it works
ASKER
JoeNuvo: what this [Sub ON] is for?
Sub is a given name/alias for resultset/table in subquery (can uses any other name)
ON is a join condition
select...
from CTE x INNER JOIN (.. sub query..) Sub ON x.Serial ....
ON is a join condition
select...
from CTE x INNER JOIN (.. sub query..) Sub ON x.Serial ....
ASKER
JoeNuvo: thank you again
you could use a CURSOR and use the Previos and Next commands to retrive you data.
example :http://www.mssqltips.com/tip.asp?tip=1599