SQLTeam.com | Weblogs | Forums

Newbie Question - Table Filtering Help for creating view

#1

Hi,
I need a little help getting some better filtering on one of the views I'm trying to work with. I can't work out a clean way to put my data (or code) in here (control + K does a hyper link?) so my apologies in advance.

Pasted image789x306 4.98 KB

What I want to do is reduce the selection to only include the lines for document ID at the max revision for that document. So for example for documentID 117711 the max revision is 6 so the first two lines should be filtered out. I've tried the code below but it doesn't work because its filtering based on the max revision number of all the document IDs rather than looking specifically at the max revision number for each document. Is there anyway to do this?

My current code below. Any help is greatly appreciated!
Best,
Nick

SELECT  *
FROM VariableValue vv

WHERE VariableID = 344 AND ConfigurationID =12 AND RevisionNo = (SELECT max(RevisionNo) From VariableValue WHERE VariableID = 344 AND ConfigurationID =12)]
#2

Could try a self join like this?

SELECT vv. *
FROM VariableValue vv
Inner join (select documentid, max(revisionno) as maxrev
From VariableValue vv2
Group by documentid) vv2 on vv.documentid = vv2. Documentid and vv.revisionno = vv2.maxrev
WHERE vv. VariableID = 344
AND vv. ConfigurationID =12

#3

Use ROW_NUMBER() for this:

SELECT VariableID, DocumentID, ProjectID, ...
FROM (
    SELECT  *, ROW_NUMBER() OVER(PARTITION BY DocumentID ORDER BY RevisionNo DESC) AS row_num
    FROM VariableValue
    --WHERE VariableID = 344
) AS derived
WHERE row_num = 1
#4

I love this version, completely forgot about row_number, and think you may have just indirectly helped me with something completely unrelated to this :slight_smile:

#5

Thanks very much for the help! I was able to do it with joins in the end. It took me a while and a few beers but I got there!
I'll find a way to use row_number soon i'm sure!