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

LougaLoAsked:
Who is Participating?
 
JoeNuvoCommented:
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

0
 
Aaron ShiloChief Database ArchitectCommented:
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
0
 
LougaLoAuthor Commented:
JoeNuvo: thank you so much. it works
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
 
LougaLoAuthor Commented:

JoeNuvo: what this [Sub ON] is for?
0
 
JoeNuvoCommented:
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 ....
0
 
LougaLoAuthor Commented:
JoeNuvo: thank you again
0
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.

All Courses

From novice to tech pro — start learning today.

Advertise Here