Get specific row from each group

Question

My question is very similar to this, except I want to be able to filter by some criteria.

I have a table "DOCUMENT" which looks something like this:

|ID|CONFIG_ID|STATE     |MAJOR_REV|MODIFIED_ON|ELEMENT_ID|
+--+---------+----------+---------+-----------+----------+
| 1|1234     |Published | 2       |2019-04-03 | 98762    |
| 2|1234     |Draft     | 1       |2019-01-02 | 98762    |
| 3|5678     |Draft     | 3       |2019-01-02 | 24244    |
| 4|5678     |Published | 2       |2017-10-04 | 24244    |
| 5|5678     |Draft     | 1       |2015-05-04 | 24244    |

It's actually a few more columns, but I'm trying to keep this simple.

For each CONFIG_ID, I would like to select the latest (MAX(MAJOR_REV) or MAX(MODIFIED_ON)) - but I might want to filter by additional criteria, such as state (e.g., the latest published revision of a document) and/or date (the latest revision, published or not, as of a specific date; or: all documents where a revision was published/modified within a specific date interval).

To make things more interesting, there are some other tables I want to join in.

Here's what I have so far:

SELECT

        allDocs.ID,
        d.CONFIG_ID,
        d.[STATE],
        d.MAJOR_REV,
        d.MODIFIED_ON,
        d.ELEMENT_ID,
        f.ID FILE_ID,
        f.[FILENAME],
        et.COLUMN1,
        e.COLUMN2

FROM DOCUMENT -- Get all document revisions

CROSS APPLY (       -- Then for each config ID, only look at the latest revision
    SELECT TOP 1
        ID,
        MODIFIED_ON,
        CONFIG_ID,
        MAJOR_REV,
        ELEMENT_ID,
        [STATE]
    FROM DOCUMENT
    WHERE CONFIG_ID=allDocs.CONFIG_ID
    ORDER BY MAJOR_REV desc
) as d

LEFT OUTER JOIN ELEMENT e ON e.ID = d.ELEMENT_ID
LEFT OUTER JOIN ELEMENT_TYPE et ON e.ELEMENT_TYPE_ID=et.ID
LEFT OUTER JOIN TREE t ON t.NODE_ID = d.ELEMENT_ID

OUTER APPLY (   -- This is another optional 1:1 relation, but it's wrongfully implemented as m:n
        SELECT TOP 1
            FILE_ID
        FROM DOCUMENT_FILE_RELATION
        WHERE DOCUMENT_ID=d.ID
        ORDER BY MODIFIED_ON DESC
        ) as df -- There should never be more than 1, but we're using TOP 1 just in case, to avoid duplicates

LEFT OUTER JOIN [FILE] f on f.ID=df.FILE_ID

WHERE
    allDocs.CONFIG_ID = '5678' -- Just for testing purposes
    and d.state ='Released'  -- One possible filter criterion, there may be others

It looks like the results are correct, but multiple identical rows are returned. My guess is that for documents with 4 revisions, the same values are found 4 times and returned.

A simple SELECT DISTINCT would solve this, but I'd prefer to fix my query.

score 0 · Accepted Answer · answered Jun 27 '19 at 00:00

0

This would be a classic row_number & partition by question I think.

;with rows as
(
    select <your-columns>,
       row_number() over (partion by config_id order by <whatever you want>) as rn
    from document
    join <anything else>
    where <whatever>
 )
 select * from rows where rn=1

answered Jun 27 '19 at 00:00

TomC

2,759
1
7
16

Thank you! That worked. Any idea why my original query returned the same result multiple times? – TravelingFox Jun 27 '19 at 08:16
Sorry I didnt look at the query too closely. Just thought there was probably a better way than using cross apply, which I think is generally a bit over-used. – TomC Jun 28 '19 at 02:16

Get specific row from each group

1 Answers1