Solved

SQL query to show previous row and current row values

Posted on 2011-03-08
6
816 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35079816
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
ID: 35079891
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
ID: 35080288
JoeNuvo: thank you so much. it works
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:LougaLo
ID: 35080320

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

Expert Comment

by:JoeNuvo
ID: 35080540
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
ID: 35080856
JoeNuvo: thank you again
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

734 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