How to join last record of table1 with table2? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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/

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

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources