Get previous record by SQL group
I have the following table:
ID GROUPID ODATE OTIME OVALUE
1 A 2014-05-31 00:00:00 1207432.6
2 A 2014-05-31 01:00:00 1209064
3 A 2014-05-31 02:00:00 1210698
4 A 2014-05-31 03:00:00 1212333.3
5 A 2014-05-31 04:00:00 1213967.7
6 B 2014-05-31 00:00:00 2110016
7 B 2014-05-31 01:00:00 2110016
8 B 2014-05-31 02:00:00 2110016
9 B 2014-05-31 03:00:00 2110016
10 B 2014-05-31 04:00:00 2110016
11 C 2014-05-31 00:00:00 2326592.6
12 C 2014-05-31 01:00:00 2328088.8
13 C 2014-05-31 02:00:00 2329590.3
14 C 2014-05-31 03:00:00 2331094.5
15 C 2014-05-31 04:00:00 2332598
Then I run this syntax:
SELECT
A.ID, A.GroupID, A.oDate, A.oTime,
A.oValue, MAX(B.oValue) AS Prev_oValue, A.oValue - MAX(B.oValue) AS oResult
FROM
Table1 AS A LEFT OUTER JOIN Table1 AS B ON B.GroupID = A.GroupID AND B.oValue < A.oValue
GROUP BY
A.ID, A.GroupID, A.oDate, A.oTime, A.oValue
ORDER BY A.GroupID, A.oDate, A.oTime
I want to get the following result:
ID GROUPID ODATE OTIME OVALUE PREV_OVALUE ORESULT
1 A 2014-05-31 00:00:00 1207432.6 (null) (null)
2 A 2014-05-31 01:00:00 1209064 1207432.6 1631.4
3 A 2014-05-31 02:00:00 1210698 1209064 1634
4 A 2014-05-31 03:00:00 1212333.3 1210698 1635.3
5 A 2014-05-31 04:00:00 1213967.7 1212333.3 1634.4
6 B 2014-05-31 00:00:00 2110016 (null) (null)
7 B 2014-05-31 01:00:00 2110016 2110016 0
8 B 2014-05-31 02:00:00 2110016 2110016 0
9 B 2014-05-31 03:00:00 2110016 2110016 0
10 B 2014-05-31 04:00:00 2110016 2110016 0
11 C 2014-05-31 00:00:00 2326592.6 (null) (null)
12 C 2014-05-31 01:00:00 2328088.8 2326592.6 1496.2
13 C 2014-05-31 02:00:00 2329590.3 2328088.8 1501.5
14 C 2014-05-31 03:00:00 2331094.5 2329590.3 1504.2
15 C 2014-05-31 04:00:00 2332598 2331094.5 1503.5
Check fiddle
I want to get the previous value based on the GroupID column and Order by Date and Time column. After I got the previous value, the current record minus the previous AS RESULT value. But something is wrong, the result is bad. Some records get the previous value and some records do not. I could not understand.
Does anyone know how to achieve this?
Thank.
+3
source to share
1 answer
You can use a common table expression with ROW_NUMBER () for the number of each row, in order of time in the group. Retrieving the previous value is as easy as left joining the cte to itself to get a row with the same group number and fewer rows. Something like:
WITH cte AS (
SELECT groupid, odate, otime, ovalue,
ROW_NUMBER() OVER (PARTITION BY groupid ORDER BY odate, otime) rn
FROM table1
)
SELECT a.groupid, a.odate, a.otime, a.ovalue, b.ovalue Prev_oValue,
a.ovalue-b.ovalue oResult
FROM cte a
LEFT JOIN cte b
ON a.groupid = b.groupid
AND a.rn = b.rn + 1
ORDER BY a.groupid, a.odate, a.otime
+2
source to share