Getting latest entry - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Getting latest entry

Author

Topic

bblgoose
Starting Member

4 Posts

Posted - 2006-12-21 : 05:25:56

I have data similar to the below:


Date                      Address     UserID      
------------------------- ----------- ----------- 
2006-12-19 20:05:53.000   447955      25
2006-12-19 20:05:50.000   447426      25
2006-12-19 15:47:06.000   381930      25
2006-12-15 07:58:12.000   447917      25
2006-09-18 19:55:16.000   447955      25
2006-09-12 09:41:19.000   338584      25
2006-08-09 10:49:00.000   310032      25
2006-12-19 22:06:24.000   446927      27
2006-12-19 22:06:23.000   447426      27
2006-12-19 22:06:15.000   334246      27
2006-12-19 18:54:52.000   446917      27

For my query, I'm only interested in addresses 446917 and 447955. What I want to do is find out which of those two addresses was last accessed by each user, and when. For the above data, the expected results are:


Date                      Address     UserID      
------------------------- ----------- ----------- 
2006-12-19 20:05:53.000   447955      25
2006-12-19 18:54:52.000   446917      27

So far, here's what I've got:


SELECT     MAX(EventTime) AS Date, Address, UserID
FROM         Events
WHERE Address=446917 OR Address=447955
GROUP BY UserID, Address
ORDER BY UserID, Date Desc

That gives me the latest date for each address per user (i.e. two rows per user). However, I'm only interested in which address was *last* accessed (one row per user).

Can anyone give me any pointers here?

Thanks

Tim

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-21 : 05:29:44

may be this?

Select Top 2 [Date], Address, ID
From Events
order by [Date] Desc

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 05:32:04

[code]select e1.*
from events e1
inner join (
select userid,
max(eventtime) mdt
from events
where address in (446917, 447955)
group by userid
) e2 on e2.userid = e1.userid and e2.mdt = e1.date[/code]
Peter Larsson
Helsingborg, Sweden

EDIT: Prefix tables

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-21 : 05:32:23

SELECT b.EventTime AS Date, a.Address, a.UserID
FROM Events a
INNER JOIN
(
select address, max(eventtime) as eventtime
from events
group by address
) b
on a.eventtime = b.eventtime and a.address = b.address
WHERE Address IN(446917, 447955)
ORDER BY a.UserID, b.eventtime Desc

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 05:36:19

Ditch, what if both addresses are last accessed by user 25?

Peter Larsson
Helsingborg, Sweden

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-21 : 05:39:11

You have a point there Peter.
Didn't think of that - Just like you didn't think of filtering on Address (Before you edited it)

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 05:42:14

Yes, I know. I read the OP badly.
I interpreted as the two addresses are the EXPECTED output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-21 : 05:45:25

Plus your joining on the id will probably be more efficient than my joining on the address.

OOOPS slipped up there trying to snipe you guys - you always seem quicker on the draw.

Duane.

bblgoose
Starting Member

4 Posts

Posted - 2006-12-21 : 05:48:40

Perfect - thanks to everyone for your help! Peter, your solution worked a treat!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources