Select the row with the maximum value in this join?

Question

I'm using SQL Server. My current query looks like this:

SELECT        
    v.DocumentID, d.Filename, toolloc.Location AS ToolLocation, 
    temploc.Location AS TemplateLocation
FROM
    SWPDM_EngVault.dbo.VariableValue AS v 
LEFT OUTER JOIN
    SWPDM_EngVault.dbo.Documents AS d ON v.DocumentID = d.DocumentID 
LEFT OUTER JOIN
    SWPDM_EngVault.dbo.DocumentsInProjects AS p ON d.DocumentID = p.DocumentID 
LEFT OUTER JOIN
    SWPDM_EngVault.dbo.Projects AS pa ON p.DocumentID = pa.ProjectID 
LEFT OUTER JOIN
    dbo.SL24ToolLocations AS toolloc ON v.DocumentID = toolloc.DocumentID 
LEFT OUTER JOIN
    dbo.SL24TemplateLocations AS temploc ON v.DocumentID = temploc.DocumentID
WHERE
    (d.Deleted <> 1) 
    AND (d.Filename LIKE '%sldasm%') 
    AND (d.Filename LIKE 'FF0%') 
    AND (d.Filename LIKE '%tool%') 
    AND (d.Filename NOT LIKE '%flami%') 
    AND (d.Filename NOT LIKE '%filami%') 
    AND (d.Filename NOT LIKE '%delete%') 
    AND (v.VariableID = 107) 
    AND (v.ValueText = 'Tool') 
    AND (v.ConfigurationID = 2)

Let's say I get 1,000 results. My SWPDM_EngVault.dbo.VariableValue has two near duplicated records except that the column "RevisionNo" has different values in them. Let's say that those are 3 and 6. I need to get query results that have a count of 998 - without the rows that have a lower RevisionNo.

Example:

DocumentID    RevisionNo
1             1
5             1
20            3
20            6
35            4
35            7
more rows ...

I only want to select the row in VariableValue that has the maximum value for RevisionNo. My current query is selecting all of them. I just want:

DocumentID    RevisionNo
1             1
5             1
20            6
35            7
more rows ...

This query is a little past my ability. how would I select the row with the max value of VariableValue.RevisionNo while still doing all the joins?

In PostgreSQL you can use `DISTINCT ON`. What database are you using? Alternative you can use `ROW_NUMBER()`, but that again depends on the specific database. — The Impaler, Jul 29 '21 at 17:55
A VariableValue row contains the DocumentID, a RevisionNo, a VariableID, a ValueText, and a ConfigurationID. Now you want only the greatest RevisionNo per DocumentID, and you want a row for a particular set of VariableID, ValueText, and ConfigurationID. Now, do you want to find the rows with matching VariableID, ValueText, and ConfigurationID and of these take tzhe row with the greatest RevisionNo? Or do you want to find the row with the greatest RevisionNo and only keep it in case it matches VariableID, ValueText, and ConfigurationID? — Thorsten Kettner, Jul 29 '21 at 18:25
On a side note: `LEFT OUTER JOIN SWPDM_EngVault.dbo.Documents AS d ... WHERE ... d.Filename LIKE '%sldasm%'` is just an obfuscated inner join, because in outer joined rows the Filename is null. And why would you outer join the documents table, anyway? The looked up row with the DocumentID should always exist - unless you don't have a foreign key constraint on v.DocumentID, which would be awful. — Thorsten Kettner, Jul 29 '21 at 18:29
Another side note: This: `p.DocumentID = pa.ProjectID` looks very wrong. — Thorsten Kettner, Jul 29 '21 at 18:29
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Jul 30 '21 at 08:55

score 0 · Answer 1 · answered Jul 29 '21 at 18:00

Assuming you are using a modern database you can use ROW_NUMBER() to filter out older revisions of the data.

For example:

select *
from (
  select 
    DocumentID,
    RevisionNo,
    row_number() over (partition by DocumentID order by RevisionNo desc) as rn,
    -- rest of the query here...
) x
where rn = 1

Select the row with the maximum value in this join?

1 Answers1