Good morning, I'm fighting with a query in sql (sql-server), I have the following table.
ValueText nvarchar(MAX) Unchecked
RevisionNo int Unchecked
ProjectID int Unchecked
DocumentID int Unchecked
VariableID int Unchecked
Where, RevisionNo
, ProjectID
, DocumentID
and VariableID
are primary keys.
RevisionNo
, refers to the current revision of a document ( DocumentID
), what I try is to obtain the value of ValueText
, for each DocumentID
, but only, bring back the last revision available in each case.
For example, making a select without modifications
SELECT DocumentId, VariableID, RevisionNo ,ValueText
FROM [PDMWE_ARTRANS].[dbo].[VariableValue]
WHERE DocumentID = 91474
I would get the following result
DocumentId VariableID RevisionNo ValueText
----------- ----------- ----------- ------------------------
91474 150 6 27/06/19
91474 238 2 Catálogo
91474 238 4 CATÁLOGO TÉCNICO
91474 265 3 EN EDICIÓN
91474 265 5 ESP. REVISIÓN
91474 265 7 ESP. APROBACIÓN
91474 265 9 APROBADO
91474 304 6 W.W
91474 351 2 CONMUTADOR BAJO CARGA
Where as you can see, for the same VariableID I have, in some cases, several results.
What I would like to get is the following:
DocumentId VariableID RevisionNo ValueText
----------- ----------- ----------- ------------------------
91474 150 6 27/06/19
91474 238 4 CATÁLOGO TÉCNICO
91474 265 9 APROBADO
91474 304 6 W.W
91474 351 2 CONMUTADOR BAJO CARGA
If the same variable exists with more than one revision, the query returns the row with the highest revision.
I tried using GroupBy and Having
SELECT DocumentId, VariableID, RevisionNo ,max(ValueText) as 'ValueText'
FROM [PDMWE_ARTRANS].[dbo].[VariableValue]
WHERE DocumentID = 91474
GROUP BY ProjectID ,DocumentId, VariableID, RevisionNo
HAVING RevisionNo = max(RevisionNo)
But I didn't succeed.
How should I approach this problem?
Thank you so much!
You can make the query in the following way:
This way you get only the line with the highest revision number for each different Document, Variable and Project.
What the query does is get the data
DocumentId, VariableID, RevisionNo, ValueText
filtered by the queryDISTINCT
I attached with theINNER JOIN
.Here I leave the Fiddle where I have done the tests: Link