Getting last entry from table together with JOIN

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

SQL Server 2005 Forums

Transact-SQL (2005)

Getting last entry from table together with JOIN

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-11 : 06:51:51

Leo writes "Hi,

I have an interesting case with two tables.

Table1 exist of columns: ID, name, active
Table2 exist of columns: ID, event, datetime
ID fields match

Table 1 holds data for each user.
Exmaple data:
1, john doe1, 1
2, john doe2, 1
3, john doe3, 0
4, john doe4, 1

Table 2 hold data for all events (event is an integer 1, 2, 3, 4, 5, 6 or 7).
Example data:
3, 7, 01/10/2004 5:00 PM
1, 1, 11/03/2006 11:30 AM
4, 7, 10/02/2006 9:01 AM
2, 4, 10/31/2006 11:32 AM
1, 5, 12/03/2006 11:31 AM
4, 2, 10/01/2006 9:01 AM

I need to get the latest event for each ID that is active in table1. Example output (based on the above example data):

john doe1, 5, 12/03/2006 11:31 AM
john doe2, 4, 10/31/2006 11:32 AM
john doe4, 7, 10/02/2006 9:01 AM

How to achieve this?
(ms sql, windows 2000)"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 06:56:09

[code]SELECT x.Name,
t.Event,
x.dt
FROM (
SELECT t1.ID,
t1.Name,
MAX(t2.DateTime) AS dt
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.ID = t1.ID
WHERE t1.Active = 1
GROUP BY t1.ID,
t1.Name
) AS x
INNER JOIN Table2 AS t ON t.ID = x.ID AND t.DateTime = x.dt
ORDER BY x.Name[/code]

Peter Larsson
Helsingborg, Sweden

l.mourits
Starting Member

1 Post

Posted - 2007-01-11 : 08:04:22

Wow, that is a swift reply

And, it works

thanks big time

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources