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

      



SQLfiddle for testing with .

+2


source







All Articles