Sort out some rows with a query

[lechuck]

I need some help whith an sql query.

the query "select date, user, location, ip from myTable"
returns this

date                     user    location    ip
-----------------------------------------------------
2003-11-08 23:32:12.000  bob     Uptime      1.2.3.4
2003-11-08 23:31:52.000  guest   Forum 	     1.2.3.99
2003-11-08 23:31:49.000  bob     Forum 	     1.2.3.4
2003-11-08 23:25:23.000  john    Guestbook   1.2.3.10
2003-11-08 23:25:20.000  guest   Guestbook   1.2.3.99
2003-11-08 23:25:17.000  john    Forum 	     1.2.3.10
2003-11-08 23:25:07.000  john    Uptime      1.2.3.10
2003-11-08 23:23:12.000  guest   Forum 	     1.2.3.88
2003-11-08 23:24:52.000  bob     Forum       1.2.3.4

Now I want a query that filter out the last place an user where (location).
So the result whould be like this:

date                     user    location    ip
-----------------------------------------------------
2003-11-08 23:32:12.000  bob     Uptime      1.2.3.4
2003-11-08 23:31:52.000  guest   Forum 	     1.2.3.99
2003-11-08 23:25:23.000  john    Guestbook   1.2.3.10
2003-11-08 23:23:12.000  guest   Forum 	     1.2.3.88

Any idea of query to fix this?
Thanks

 

[Spellman]

Haven't tried this but it should be something like::

Select max(date), user, location, ip from mytable
group by user

Mark

The key to immortality is to make a big impression in this life!!

 

[nigelrivett]

select date, user, location, ip from myTable t
where date = (select max(t2.date) from myTable t2 where t.location = t2.location)


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

 

[sunila7]

Try this:

select * from mytable M
Inner join
(SELECT Min(date) dt,[user],ip From myTable
group by [user],ip) TBL on M.date=Tbl.dt and m.[user]=tbl.[user] and m.ip=tbl.ip

Sunil