CODEWITHSUNDEEP
sqlsql-server
Simon
Simon

Reputation: 1211

Returning latest record for each column

Sample Table

+--------+------------+------------------+-------------+-------------+
| FileID |    Date    |     Activity     | Assigned_By | Responsible |
+--------+------------+------------------+-------------+-------------+
|    123 | 2016/01/01 | Work in progress | Foo1        | Bob         | 
|    234 | 2016/01/01 | Work in progress | Foo2        | Smith       | 
|    123 | 2016/01/02 | Escalated        | NULL        | NULL        | 
|    123 | 2016/01/03 | Need reassign    | NULL        | NULL        | 
|    123 | 2016/01/03 | Reassigned       | Foo2        | John        | 
|    234 | 2016/01/03 | Completed        | NULL        | NULL        |
|    123 | 2016/01/04 | Completed        | NULL        | NULL        |
+--------+------------+------------------+-------------+-------------+

My query:

SELECT FileID,
       Date,
       Activity,
       Assigned_By,
       Responsible
FROM (
      SELECT fooTable.*, ROW_NUMBER() OVER (PARTITION BY FileID ORDER BY Date DESC) AS Separator
     ) fooTable
INNER JOIN randomTable ON fooTable.FileID = randomTable.ID
WHERE fooTable.Separator = 1;

Returns:

+--------+------------+-----------+-------------+-------------+
| FileID |    Date    | Activity  | Assigned By | Responsible |
+--------+------------+-----------+-------------+-------------+
|    234 | 2016/01/03 | Completed | NULL        | NULL        |
|    123 | 2016/01/04 | Completed | NULL        | NULL        |
+--------+------------+-----------+-------------+-------------+

Desired results - Returning each row with the LATEST column record of each unique FileID with the latest DATE:

+--------+------------+-----------+-------------+-------------+
| FileID |    Date    | Activity  | Assigned By | Responsible |
+--------+------------+-----------+-------------+-------------+
|    234 | 2016/01/03 | Completed | Foo2        | John        |
|    123 | 2016/01/04 | Completed | Foo1        | Bob         |
+--------+------------+-----------+-------------+-------------+

I kind of understand why the query isn't working, because it is only returning the latest row (assigned 1 by row_number), so therefore I will receive the first ROW record for that unique FileID based on the descending date. But I don't know how to fix it.

EDIT: Something else I realized is that MAX() will not work for Assigned_By and Responsible (I think) since it will return the greater alphabetical name...

Upvotes: 2

Views: 105

Answers (2)

Hogan
Hogan

Reputation: 70513

You use joins or you can use FIRST_VALUE, like this:

SELECT 
  FileID,
  FIRST_VALUE(Date) OVER (PARTITION BY FileID ORDER BY Date DESC ROWS  BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Date,
  FIRST_VALUE(Activity) OVER (PARTITION BY FileID ORDER BY CASE WHEN Activity IS NULL THEN 0 ELSE 1 END DESC, Date DESC ROWS  BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Activity,
  FIRST_VALUE(Assigned_By) OVER (PARTITION BY FileID ORDER BY CASE WHEN Assigned_By IS NULL THEN 0 ELSE 1 END DESC, Date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Assigned_By,
  FIRST_VALUE(Responsible) OVER (PARTITION BY FileID ORDER BY CASE WHEN Responsible IS NULL THEN 0 ELSE 1 END DESC, Date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Responsible
FROM fooTable
INNER JOIN randomTable ON fooTable.FileID = randomTable.ID
WHERE fooTable.Separator = 1;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can do what you want using conditional aggregation:

WITH t AS (
      SELECT FileID, Date, Activity, Assigned_By, Responsible
      FROM fooTable INNER JOIN 
           randomTable
           ON fooTable.FileID = randomTable.ID
     )
SELECT FileID, MAX(Date) as date,
       MAX(CASE WHEN seqnum = 1 THEN Activity END) as Activity,
       MAX(CASE WHEN seqnum_nonnull = 1 THEN Assigned_By END) as Assigned_By,
       MAX(CASE WHEN seqnum_nonnull = 1 THEN Responsible END) as Responsible
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY FileID ORDER BY Date DESC) AS seqnum,
             ROW_NUMBER() OVER (PARTITION BY FileID
                                ORDER BY (CASE WHEN AssignedBy IS NOT NULL THEN 1 ELSE 2 END), Date DESC
                               ) AS seqnum_notnull
      FROM t
     ) t
GROUP BY FileID;

Upvotes: 2

Related Questions