Here is my query :
SELECT *
FROM a
INNER JOIN b ON b.id_a = a.id
INNER JOIN c ON c.id_b = b.id
INNER JOIN d ON d.id_c = c.id
INNER JOIN e ON e.id = d.id_e
My problem is it returns some rows which are excatly the same except for the 'c' table values. I need to take the max of one column of 'c' but I don't know how exactly.
Example of what this query returns :
a.fields b.fields c.id c.gamut c.position d.fields e.fields
--------------------------------------------------------------------------
... ... 1 2 1 ... ...
... ... 2 2 2 ... ...
... ... 3 2 3 ... ...
... ... 4 3 1 ... ...
... ... 4 3 2 ... ...
What i need to have :
a.fields b.fields c.id c.gamut c.position d.fields e.fields
--------------------------------------------------------------------------
... ... 3 2 3 ... ...
... ... 4 3 2 ... ...
I need to get the max position for the same gamut.
Information:
This is a simple application of the ROW_NUMBER() function:
;WITH Ordered AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY c.gamut ORDER BY c.position desc) as rn
FROM a
INNER JOIN b ON b.id_a = a.id
INNER JOIN c ON c.id_b = b.id
INNER JOIN d ON d.id_c = c.id
INNER JOIN e ON e.id = d.id_e
)
select * from Ordered where rn = 1
(If there are multiple rows with the same max Position for the same gamut, this will select an arbitrary one. If there are further tie-breaking columns, add those to the ORDER BY. If you want the ties to show up, switch to RANK() rather than ROW_NUMBER())
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments