troubleshooting Question

SQL query to show previous row and current row values

Avatar of LougaLo
LougaLoFlag for United States of America asked on
Microsoft SQL Server
6 Comments1 Solution843 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
JoeNuvo

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros