CODEWITHSUNDEEP
sqlsql-servert-sql
Tam
Tam

Reputation: 25

How to get the max version records?

I have a table like the following:

------------------------------------
Id   FId   UId   Version
1    1     1     1
2    1     2     1
3    1     3     1
4    1     2     2
5    1     3     2
6    1     3     2
7    1     4     2
8    2     1     1
9    2     2     1

then I want the result to be:

--------------------------
FId  UId   Version
1    2     2
1    3     2
1    4     2
2    1     1
2    2     1

How to write the query based on the max 'Version' of each FId-UId pair?

Upvotes: 1

Views: 7987

Answers (4)

jeroenh
jeroenh

Reputation: 26782

select FId, UId, Version 
from MyTable
join (select Fid, Max(Version) as MaxVersion group by Fid) x 
on x.FId = MyTable.FId and x.MaxVersion = MyTable.Version

Upvotes: 1

josephj1989
josephj1989

Reputation: 9709

Is the result you show correct- 1,3,2 should appear twice.If you need only once use select distinct

The foll query is working

with t as(
select 1 as id,   1 as fid  ,  1  as uid,   1 as version union all
select 2 ,   1 ,    2 ,    1  union all
select 3 ,   1  ,   3 ,    1  union all
select 4 ,   1 ,    2   ,  2  union all
select 5  ,  1  ,   3  ,   2  union all
select 6 ,   1  ,   3  ,  2  union all
select 7 ,   1 ,    4  ,   2  union all
select 8  ,  2  ,   1  ,   1  union all
select 9 ,   2  ,   2  ,   1)

select distinct t.fid,t.uid,t.version from t 
inner join(
select fid,max(version) as maxversion from t
group by fid)as grp
on t.fid=grp.fid 
and t.version=grp.maxversion

Upvotes: 0

Jon
Jon

Reputation: 5357

The following gives the output requested.

select distinct t2.FId, t2.UId, t2.Version
from
(
    select FId, max(Version) as "Version"
    from MyTable
    group by FId
) t1
inner join MyTable t2 on (t1.FId = t2.FId and t1.Version = t2.Version)
order by t2.FId, t2.UId

Upvotes: 4

Ed Harper
Ed Harper

Reputation: 21505

This will work on SQL 2005 and later:

DECLARE @t TABLE
(Id INT,
Fid INT,
[uid] INT,
[VERSION] INT
)

INSERT @t
SELECT 1,1,1,1
UNION ALL SELECT 2,1,2,1
UNION ALL SELECT 3,1,3,1
UNION ALL SELECT 4,1,2,2
UNION ALL SELECT 5,1,3,2
UNION ALL SELECT 6,1,3,2
UNION ALL SELECT 7,1,4,2
UNION ALL SELECT 8,2,1,1
UNION ALL SELECT 9,2,2,1

;WITH myCTE
AS
(
    SELECT *,
           RANK() OVER (PARTITION BY Fid
                        ORDER BY [VERSION] DESC
                       ) AS rnk
    FROM @t
)
SELECT DISTINCT Fid, [uid],[VERSION]
FROM myCTE
WHERE rnk = 1
ORDER BY Fid, [uid]

Upvotes: 2

Related Questions