CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
espresso_coffee
espresso_coffee

Reputation: 6110

How to join only the most recent record in SQL Server 2008?

I have two tables A and B. Table A can have multiple records for each user. Table B can have multiple records for each row in table A.

Here is an example:

    Table A                 Table B
   a_id   a_key        b_id   b_key  b_AtblID
1. 6678   5778      1. 6509    5778    6678
2. 6679   5778      2. 6508    5778    6678
                    3. 6507    5778    6679
                    4. 6505    5778    6679
                    5. 6490    5778    6678

You can see 2 records in A table. One record has 3 records in B table and one have 2 records in B table.

I want my query to pull records from table A and the most recent record from B table. I don't need every single record from Table B that is matching record in table A if that makes sense. Here is example of what I have but my code pulled all records from table B:

SELECT *
FROM A
     LEFT OUTER JOIN B
        ON b_AtblID = (
            SELECT TOP 1 b_AtblID
            FROM B 
            WHERE b_AtblID = a_id
            ORDER BY b_id DESC
        )

I was hoping that my code will return only one record for each row on table A but I got all records from table B. If anyone can help please let me know. Thanks.

Upvotes: 1

Views: 74

Answers (2)

Kapil
Kapil

Reputation: 987

Create table #tempA (a_id int,a_key int)
Create table #tempB (b_id int,b_key int,b_abtlid int)
Insert into #tempA values(6678,5778),(6679,5778)
Insert into #tempB values(6509,5778,6678),(6508,5778,6678),(6507,5778,6679),(6505,5778,6679),(6490,5778,6678)


select a_id,a_key,b_id,b_abtlid from (
select *,row_number() over(partition by a_id order by b_id desc )as Rn from #tempA a
inner join #tempB b
on a.a_id=b.b_abtlid ) t 
where t.Rn=1

Upvotes: 0

Lamak
Lamak

Reputation: 70648

You can use ROW_NUMBER:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY b_AtblID ORDER BY b_id DESC)
    FROM dbo.TableB
)
SELECT *
FROM dbo.TableA a
INNER JOIN CTE b
    ON a.a_id = b.b_AtblID
    AND b.RN = 1
;

Or CROSS APPLY:

SELECT *
FROM dbo.TableA a
CROSS APPLY (SELECT TOP 1 *
             FROM dbo.TableB
             WHERE b_AtblID = a.a_id
             ORDER BY b_id DESC) b;

Upvotes: 5

Related Questions