Geek asks and answers

How to get the latest and latest recent values ​​in SQL Server

I have a table containing data related to Risks, and I should get the last and last 2 last data from the risk table

The data in the table is as follows:

RiskID        RiskName     RiskScore       RiskDate    ItemID      
1             ABC            10          23/10/2013       12            
1             ABC            8           20/12/2013       12 
1             ABC            5           15/01/2014       12
2             BC             9           19/09/2013       12
2             BC             10          17/12/2013       12
2             BC             9           12/01/2014       12

Where I am looking for the result, as shown below, where ItemID should be 12

R_ID   R_Name  Recent_R_Date  Recent_R_Score  Second_R_Date   Second_R_Date  ItemID
1       ABC       15/01/2014      5              20/12/2013       8           12
2       BC        12/01/2014      9             17/12/2013        10          12
+3
source share
2 answers

One way to do this (which also works in SQL Server 2008)

SELECT RiskID, RiskName,
       MAX(CASE WHEN rnum = 1 THEN RiskDate END) RecentDate,
       MAX(CASE WHEN rnum = 1 THEN RiskScore END) RecentScore,
       MAX(CASE WHEN rnum = 2 THEN RiskDate END) SecondDate,
       MAX(CASE WHEN rnum = 2 THEN RiskScore END) SecondScore,
       ItemID
  FROM
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY RiskID ORDER BY RiskDate DESC) rnum
    FROM Risk
   WHERE ItemID = 12
) q
 WHERE rnum <= 2
 GROUP BY ItemID, RiskID, RiskName
 ORDER BY RiskID

Conclusion:

| RISKID | RISKNAME | RECENTDATE | RECENTSCORE | SECONDDATE | SECONDSCORE | ITEMID |
| -------- | ---------- | ------------ | ------------- | - ---------- | ------------- | -------- |
| 1 | ABC | 2014-01-15 | 5 | 2013-12-20 | 8 | 12 |
|      2 |       BC | 2014-01-12 |           9 | 2013-12-17 |          10 |     12 |

SQLFiddle

+3

Edit:

sql-server-2012, LEAD LAG, "" ' . , "" RiskName.

SELECT TOP 2
    RiskId, RiskName, RiskDate AS Recent_R_Date, RiskScore AS Recent_R_Score, 
    LAG(RiskDate) OVER (PARTITION BY RiskName ORDER BY RiskDate ASC) AS Second_R_Date,
    LAG(RiskScore) OVER (PARTITION BY RiskName ORDER BY RiskDate ASC) AS Second_R_Score,
    ItemID
 FROM Risk
 WHERE ItemID = 12
 ORDER BY RiskDate DESC;

, DESC, 2, :   SELECT TOP 2       R_ID, R_Name, Recent_R_Date, Recent_R_Score,       Second_R_Date, Second_R_Date, ItemID        WHERE ItemID = 12    ORDER BY RiskDate DESC; >
+3

More articles:


All Articles