I got a table like this :
value | version1 | version2 | version3
--------|----------|----------|---------
aaa| 1| 1| 1
aaa| 1| 1| 2
aaa| 1| 2| 1
aaa| 1| 2| 2
aaa| 3| 1| 1
aaa| 3| 2| 1
aaa| 3| 2| 2
bbb| 1| 1| 1
bbb| 2| 1| 2
bbb| 2| 2| 1
ccc| 2| 1| 2
ccc| 3| 1| 1
ccc| 3| 2| 1
ccc| 3| 4| 1
What I want to do, is write a query that only selects the last record. With one version field it's easy to do with just a max function, but how can I do this with 3 version fields?
In this case, the "version1" field has the highest priority and "version3" the lowest, logicaly "version2" is in the middle. For example, a version1 with a value of 2 always has priority over a version3 with a a value of 10 when his version1 is 1.
So in above table, I want to select the following records :
value | version1 | version2 | version3
--------|----------|----------|---------
aaa| 3| 2| 2
bbb| 2| 2| 1
ccc| 3| 4| 1
first look at the highest version1, then the highest version2 and then the highest version3.
Can someone please tell me what the most efficient way to do this is?
thx in advance!
Edited:
Looking at your further explanation, you need to partition your output per different value.
You can then use the ROW_NUMBER() function, limiting the output to just one row per different value.
Example:
SELECT
value, version1, version2, version3
FROM (
SELECT
ROW_NUMBER() OVER (
PARTITION BY value
ORDER BY version1 DESC, version2 DESC, version3 DESC
) RN,
value, version1, version2, version3
FROM tableName
) T
WHERE RN = 1
ROW_NUMBER() function will give an incremental number to each row of the result set,
while PARTITION BY clause will give an independent row number for each set of different values.
ORDER BY works by sorting your records using the first value declared (version 1), then skipping to second value in case of exact match of first value, and so on...
DESC keyword indicates to sort from higher to lower value.
Applying the final WHERE condition does the magic: just the top row for each different value is returned.
This might help you :
select value,version1,version2,version3
from
(
select *,row_number() over(partition by value order by version1 desc, version2 desc, version3 desc) as rn
from yourtable
) as t
where rn = 1
You can use ROW_NUMBER() to partition the data over the value field and specify the ordering on the columns as required. Using this method, you are effectively ranking the rows in order with a row number per value group, and you can simply take the rows where the row number = 1 per value:
SQL Fiddle Demo
Schema Setup:
CREATE TABLE Versions
([value] varchar(3), [version1] int, [version2] int, [version3] int)
;
INSERT INTO Versions
([value], [version1], [version2], [version3])
VALUES
('aaa', 1, 1, 1),
('aaa', 1, 1, 2),
('aaa', 1, 2, 1),
('aaa', 1, 2, 2),
('aaa', 3, 1, 1),
('aaa', 3, 2, 1),
('aaa', 3, 2, 2),
('bbb', 1, 1, 1),
('bbb', 2, 1, 2),
('bbb', 2, 2, 1),
('ccc', 2, 1, 2),
('ccc', 3, 1, 1),
('ccc', 3, 2, 1),
('ccc', 3, 4, 1)
;
Query using ROW_NUMBER():
SELECT *
FROM ( SELECT value ,
version1 ,
version2 ,
version3 ,
ROW_NUMBER() OVER ( PARTITION BY value
ORDER BY version1 DESC,
version2 DESC,
version3 DESC ) AS RowIdentifier
FROM dbo.Versions
) t
WHERE RowIdentifier = 1
Results:
| VALUE | VERSION1 | VERSION2 | VERSION3 | ROWIDENTIFIER |
|-------|----------|----------|----------|---------------|
| aaa | 3 | 2 | 2 | 1 |
| bbb | 2 | 2 | 1 | 1 |
| ccc | 3 | 4 | 1 | 1 |
