user1916528 user1916528 - 2 months ago 7
SQL Question

How to query the most recent record in SQL Server 2012?

I am attempting to query the most recent status of a a document, but not having any success. The tables I am using for my query are

docs
,
docStats
and
statusList
.

docs
table:

| docId | docTitle | venIdFk |
+-------+------------+---------+
| 1 | Contract 1 | 81 |
| 2 | Contract 2 | 37 |
+-------+------------+---------+


docStats
table:

| docStatIdPk | docStat | docStatDt | docIdFk |
+-------------+---------+-------------------------+---------+
| 7 | 1 | 2016-09-16 09:00:00.000 | 1 |
| 10 | 2 | 2016-09-16 09:30:00.000 | 1 |
| 11 | 4 | 2016-09-17 08:30:00.000 | 1 |
| 12 | 1 | 2016-09-17 10:00:00.000 | 2 |
+-------------+---------+-------------------------+---------+


statusList
table:

| statId | stat |
+--------+--------------+
| 1 | Needs Review |
| 2 | In Review |
| 3 | Denied |
| 4 | Accepted |
+--------+--------------+


Using the following I can get the most recent status of the documents in the docs table:

SELECT
d2.docId, MAX(ds2.docStatDt) AS maxStatDt
FROM
docs d2
INNER JOIN
docStats ds2 ON d2.docId = ds2.docIdFk
GROUP BY
d2.docId


The result is correct:

| docId | maxStatDt |
+-------+-------------------------+
| 1 | 2016-09-17 08:30:00.000 |
| 2 | 2016-09-17 10:00:00.000 |
+-------+-------------------------+


But I need to add additional columns to the query, and this is where it breaks. It adds all of the records for docId 1 because 'stat' is included in the SELECT, and there are multiple statuses for docId 1. How can I write this query so that it only returns the most recent status of docId 1?

Here is the query as I have it now:

SELECT
d.docid, d.doctitle, d.doctype, d.docorg, d.docdept,
s.stat,
mdt.maxdate AS crntDocStatDtSet
FROM
docs d
INNER JOIN
docstats ds ON d.docid = ds.docidfk
INNER JOIN
statuslist s ON ds.docstat = s.statid
INNER JOIN
(SELECT
d2.docid, Max(ds2.docstatdt) AS MaxDate
FROM
docs d2
INNER JOIN
docstats ds2 ON d2.docid = ds2.docidfk
GROUP BY
d2.docid) mdt ON d.docid = mdt.docid
WHERE
d.docid = '1'
GROUP BY
d.docid, d.doctitle, d.doctype, d.docorg, d.docdept,
s.stat, mdt.maxdate


Here are the results:

| docId | docTitle | docType | docOrg | docDept | stat | crntDocStatDtSet |
+-------+------------+---------+--------+---------+--------------+-------------------------+
| 1 | Contract 1 | 3 | 3 | 2 | Accepted | 2016-09-17 08:30:00.000 |
| 1 | Contract 1 | 3 | 3 | 2 | In Review | 2016-09-17 08:30:00.000 |
| 1 | Contract 1 | 3 | 3 | 2 | Needs Review | 2016-09-17 08:30:00.000 |
+-------+------------+---------+--------+---------+--------------+-------------------------+

Answer
;WITH cte AS (
    SELECT
       d.docId, 
       d.docTitle, 
       d.doctype, 
       d.docOrg, 
       d.docDept, 
       s.stat,
       ROW_NUMBER() OVER (PARTITION BY d.docId ORDER BY ds.docStatDt DESC) as RowNumber
    FROM
       docs d
       INNER JOIN docStats ds
       ON d.docId = ds.docIdFk
       INNER JOIN statusList s
       ON ds.docStat = s.statId
)

SELECT *
FROM
    cte
WHERE
    RowNumber = 1

It would be better to use a partitioned ROW_NUMBER(). MAX() the way you are using it could end up with more than 1 result. If more than 1 would be desired if they are equal then simply use DENSE_RANK() in place of where ROW_NUMBER() is.

The route you are going is also a valid method but your ON condition for your join did not have ON ds.docStatDt = mdt.MaxDate which meant you were not actually limiting your results to the last record. Adding that condition in would probably give you what you want as well.

 SELECT
    d.docId, 
    d.docTitle, 
    d.doctype, 
    d.docOrg, 
    d.docDept, 
    s.stat,
    mdt.MaxDate AS crntDocStatDtSet
FROM
    docs d
    INNER JOIN docStats ds
    on d.docId = ds.docIdFk
    INNER JOIN statusList s
    ON ds.docStat = s.statId
    INNER JOIN (
       SELECT d2.docId, MAX(ds2.docStatDt) as MaxDate
       FROM docs d2
       INNER JOIN docStats ds2
       ON d2.docId = ds2.docIdFk
       GROUP BY d2.docId
    ) mdt
    ON d.docId = mdt.docId

    AND ds.docStatDt = mdt.MaxDate

WHERE d.docId = '1'
GROUP BY
    d.docId, 
    d.docTitle, 
    d.doctype, 
    d.docOrg, 
    d.docDept, 
    s.stat, 
    mdt.MaxDate