Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.
Course Of The Month
10 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL query to show previous row and current row values
Posted on 2011-03-08
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
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
[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
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
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
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
Featured Post
Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month10 days, 6 hours left to enroll
628 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.