Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to join last record of table1 with table2?

Author  Topic 

huub
Starting Member

4 Posts

Posted - 2014-10-17 : 11:15:41
HOW TO SELECT LAST ROW IN GROUP BY CLAUSE AND JOIN LAST ROW WITH ANOTHER TABLE IN ONE QUERY?

INPUT:
custid livingstatus date
1 single 2014-01-01 00:00:00.000
1 married 2014-01-02 00:00:00.000
1 married_kids 2014-01-03 00:00:00.000
2 married_kids 2014-01-04 00:00:00.000
2 married 2014-01-05 00:00:00.000
2 single 2014-01-06 00:00:00.000

Query 1 -- select last record
SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Custid ORDER BY Date DESC) AS Seq,*
FROM Living_Situation
)t
WHERE Seq=1

Output:
Seq custid livingstatus date
1 1 married_kids 2014-01-03 00:00:00.000
1 2 single 2014-01-06 00:00:00.000

Table Customer:

custid sexe surname
1 m jansen
2 m pietersen

How to link Query 1 with table Customer in one query?:

Desired output in ONE query:

custid sexe surname livingstatus
1 m jansen married_kids
2 m pietersen single

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-17 : 11:46:23
[code]
SELECT ot.custid, ot.sexe, ot.surname, t.livingstatus
FROM
( SELECT ROW_NUMBER() OVER(PARTITION BY Custid ORDER BY Date DESC) AS Seq, *
FROM Living_Situation
) t
JOIN OtherTable ot ON t.custid = ot.custid
WHERE t.Seq=1
[/code]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

huub
Starting Member

4 Posts

Posted - 2014-10-18 : 09:10:08
Hi Tara,
I am very happy with your quick reply. Yes it works.
Thank you very much!
Regards,
Huub Cremers, Amstelveen (near Amsterdam), Netherlands
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-20 : 11:56:37


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -