Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 824
  • Last Modified:

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

0
LougaLo
Asked:
LougaLo
  • 3
  • 2
1 Solution
 
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
 
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
 
LougaLoAuthor Commented:
JoeNuvo: thank you so much. it works
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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