Have a table with following structure
ID DeviceName Status
1 Sony Good
2 Toshiba OK
3 Sony Bad
4 Tata OK
I need to return the following records
ID DeviceName Status
2 Toshiba OK
3 Sony Bad
4 Tata OK
If there are more than one record for the Device, then record with the latest ID should be returned.
If there is only one record for the Device, then that record should be returned.
Can this be achieved through a single query.
Any help is appreciated.
Thanks,
Loonysan
SQL Query - Fetching Records
StriderIRL
One way
create table #test (ID int, DeviceName varchar(49), Status varchar(51))
insert #test
select 1, 'Sony ', 'Good' union all
select 2, 'Toshiba',' OK' union all
select 3 ,'Sony' , 'Bad' union all
select 4 , 'Tata', 'OK'
select t1.* from(
select max(id)as maxid,devicename from #test
group by devicename) t2 join #test t1 on t1.devicename= t2.devicename
and t1.id =t2.maxid
order by t1.id
Denis The SQL Menace
http://sqlservercode.blogspot.com/