CODEWITHSUNDEEP
sql-server
theo
theo

Reputation: 299

SQL Server : get the latest record based on a specific column from two tables

I have two tables with the same table design.

tblSpeaker1:

---------------------
|ID|Speaker |Version|
|--+--------+-------|
|B1|Xiong   |1      |
|B1|Ling    |2      |
|B1|Juyng   |4      |
|G4|Micre   |1      |
|G4|Pyong   |2      |
|Y1|Mong    |2      |
---------------------

tblSpeaker2:

---------------------
|ID|Speaker |Version|
|--+--------+-------|
|B1|Looke   |3      |
|G4|Chui    |3      |
|G4|Kyui    |4      |
|Y1|Druke   |1      |
---------------------

What I want to achieve is to get the latest record of each ID based on the version no but without the Version column.

Currently, I have this query:

SELECT TOP 1 ID, Speaker 
FROM
    (SELECT * FROM tblSpeaker1
     UNION
     SELECT * FROM tblSpeaker2)
ORDER BY Version

But it only returns:

-------------
|ID|Speaker |
|--+--------|
|B1|Juyng   |
-------------

This should be the output

-------------
|ID|Speaker |
|--+--------|
|B1|Juyng   |
|G4|Kyui    |
|Y1|Mong    |
-------------

Please help me. Thank you.

Upvotes: 1

Views: 68

Answers (3)

Ruslan K.
Ruslan K.

Reputation: 1981

Use OVER clause:

WITH united AS
(
    SELECT *
        FROM (VALUES ('B1', 'Xiong', 1),
                     ('B1', 'Ling', 2),
                     ('B1', 'Juyng', 4),
                     ('G4', 'Micre', 1),
                     ('G4', 'Pyong', 2),
                     ('Y1', 'Mong', 2)) AS tblSpeaker1(ID, Speaker, Version)
    UNION
    SELECT *
        FROM (VALUES ('B1', 'Looke', 3),
                     ('G4', 'Chui', 3),
                     ('G4', 'Kyui', 4),
                     ('Y1', 'Druke', 1)) AS tblSpeaker2(ID, Speaker, Version)
),
numbered AS
(
    SELECT *, row_number() OVER(PARTITION BY ID ORDER BY Version desc) rn 
        FROM united
)
SELECT ID, Speaker
    FROM numbered
    WHERE rn = 1;

Output:

ID   Speaker
---- -------
B1   Juyng
G4   Kyui
Y1   Mong

Upvotes: 2

Jonathan Willcock
Jonathan Willcock

Reputation: 5255

This is a situation where you want to use PARTITION BY. I give an illustration below:

declare @tblSpeaker1 table
(
id char(2),
speaker varchar(10),
version int
) 
declare @tblSpeaker2 table
(
id char(2),
speaker varchar(10),
version int
) 

insert into @tblSpeaker1 VALUES
('B1','Xiong',1),
('B1','Ling',2),
('B1','Juyng',4),
('G4','Micre',1),
('G4','Pyong',2),
('Y1','Mong',2)

insert into @tblSpeaker2 VALUES
('B1','Looke',3),
('G4','Chui',3),
('G4','Kyui',4),
('Y1','Druke',1)

SELECT id, speaker FROM
(select id, speaker, version, 
ROW_NUMBER() OVER (PARTITION BY id ORDER BY version DESC) as rn from
(select id, speaker, version FROM @tblSpeaker1
union
select id, speaker, version FROM @tblSpeaker2) u) r
WHERE r.rn = 1

So that you understand what is happening here. Firstly you need to UNION the two tables (as you were doing). Next we use the row_number() function combined with partition by to get numbering by id, with the order on version desc. Finally we take those values whose row number is 1.

Upvotes: 4

Thiago Loureiro
Thiago Loureiro

Reputation: 1143

    declare @tbResult table  (ID varchar(2), Speaker varchar(50))
declare @IDTables table (idx int identity(1,1), ID varchar(20))
declare @Counter int
declare @Categories int
declare @Speakers table (ID varchar(2), Speaker varchar(50), Version int)

set @Counter = 1

insert into @Speakers 
select ID, Speaker, Version from tblSpeaker1
insert into @Speakers 
select ID, Speaker, Version from tblSpeaker2

insert into @IDTables
select distinct ID from tblSpeaker1
union 
select distinct ID from tblSpeaker2

set @Categories = (select count(ID) from @IDTables)

while (@Counter <= @Categories)
begin
    declare @ID varchar(2)
    set @ID = (Select ID from @IDTables where idx = @Counter)

    insert into @tbResult
    select top 1 ID, Speaker from @Speakers
    where ID = @ID order by Version Desc

    set @Counter = @Counter + 1
end


select * from @tbResult

Upvotes: 1

Related Questions