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

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

Open in new window

LougaLo

Asked: 2011-03-08

Advertise Here

Microsoft SQL Server

1 Solution

LVL 15

Aaron ShiloChief Database ArchitectCommented: 2011-03-08

hi

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

LVL 11

JoeNuvoCommented: 2011-03-08

How about this code?

WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY pid, MDATE ) Serial FROM #table
)
SELECT x.*, CASE x.Serial WHEN Sub.Serial THEN 'FLAG' ELSE '' END PLAG
FROM CTE x
INNER JOIN
(
	SELECT a.*, b.Serial SerialB
	FROM CTE a
		LEFT JOIN CTE b ON a.Serial - 1 = b.Serial AND a.pid = b.pid
		WHERE b.MHEIGHT > a.MHEIGHT
) Sub ON x.Serial IN (Sub.Serial, Sub.SerialB)
ORDER BY x.Serial

Open in new window

LougaLoAuthor Commented: 2011-03-09

JoeNuvo: thank you so much. it works

LougaLoAuthor Commented: 2011-03-09

JoeNuvo: what this [Sub ON] is for?

LVL 11

JoeNuvoCommented: 2011-03-09

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

LougaLoAuthor Commented: 2011-03-09

JoeNuvo: thank you again

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Expand your skills with this month's free Premium course.

SQL query to show previous row and current row values

Join & Write a Comment Already a member? Login.

Featured Post