Select Latest - SQL Server Forums

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

Select Latest

Author

Topic

ajboss
Starting Member

2 Posts

Posted - 2010-12-03 : 09:02:57

Table A
-------
ID_NO
USER

Table B
-------

ID_NO
LOCATION
CREATED_DATE

Example Data
------------

Table A
--------
AB1234, JBLOGGS
AB1235, GBUSH

Table B
-------

AB1234, USA, 01/01/2010
AB1234, UK, 02/02/2010
AB1235, GBUSH, 01/01/2010

I want to select all the fields from table A and only the latest (CREATED_DATE as latest) fields from table B. For e.g.

AB1234, JBLOGGS, UK, 01/01/2010
AB1235, GBUSH, 01/01/2010

Any ideas?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 09:11:58

select a.id_no, ...
from tbla a
join tblb b
on a.id_no = b.id_no
join (select id_no, creatred = max(created) from tblb group by id_no) bmax
on b.id_no = bmax.id_no
and b.created = bmax.created

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ajboss
Starting Member

2 Posts

Posted - 2010-12-03 : 09:20:42

Thank you very much. Works perfect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources