Solved

SQL query to show previous row and current row values

Posted on 2011-03-08
6
808 Views
Last Modified: 2012-08-14
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
Comment
Question by:LougaLo
  • 3
  • 2
6 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
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
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
Comment Utility
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
 

Author Comment

by:LougaLo
Comment Utility
JoeNuvo: thank you so much. it works
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:LougaLo
Comment Utility

JoeNuvo: what this [Sub ON] is for?
0
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
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
 

Author Comment

by:LougaLo
Comment Utility
JoeNuvo: thank you again
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now