Example: I have a table in the following format:

ID | LoginTime | IPaddress
2 | 12/05/05 | 255.255.255.255
3 | 12/05/05 | 132.123.123.123
2 | 12/06/05 | 255.255.255.255
3 | 12/06/05 | 132.123.123.123
2 | 12/07/05 | 255.255.255.255
3 | 12/07/05 | 132.123.123.123

I'd like to return only the most current record for each ID.

I can get just the ID by doing:

SELECT DISTINCT Id
FROM tablename

My question is, how do I get the unique id, but also the rest of the
columns as well without making them part of the UNIQUE constraint?

Thanks

One way

create table testip (id int, logintime datetime,IPaddress varchar(50))

insert into testip
select 2 , '12/05/05' , '255.255.255.255' union all
select 3 , '12/05/05' , '132.123.123.123' union all
select 2 , '12/06/05' , '255.255.255.255' union all
select 3 , '12/06/05' , '132.123.123.123' union all
select 2 , '12/07/05' , '255.255.255.255' union all
select 3 , '12/07/05' , '132.123.123.123'

select t1.id,t1.logintime,t1.IPaddress
from testip t1
join (select id,max(logintime) as logintime from testip
group by id) t2 on t1.id =t2.id and t1.logintime = t2.logintime

http://sqlservercode.blogspot.com/

[quoted text, click to view]