We help IT Professionals succeed at work.

MS SQL Select distinct

webcertified
webcertified asked
on
Hello,

I have following table with data.

service_id  service_start           service_end             updated_by last_updated
----------- ----------------------- ----------------------- ---------- -----------------------
1           2010-06-30 00:00:00.000 2010-06-30 00:00:00.000 test       2010-06-09 14:45:31.367
2           2010-06-30 00:00:00.000 2010-06-30 00:00:00.000 test       2010-06-09 14:47:17.163
3           2010-07-31 00:00:00.000 2010-07-31 00:00:00.000 test       2010-06-09 14:49:16.210
4           2010-07-01 00:00:00.000 2010-07-01 00:00:00.000 test       2010-06-09 14:49:16.210
6           2010-07-01 00:00:00.000 2010-07-01 00:00:00.000 test       2010-06-10 14:49:16.210
7           2010-07-31 00:00:00.000 2010-07-31 00:00:00.000 test       2010-06-09 14:53:16.210

I would like to select distinct service_start with latest update. So when I do a select it should look like

service_id  service_start           service_end             updated_by last_updated
----------- ----------------------- ----------------------- ---------- -----------------------
2           2010-06-30 00:00:00.000 2010-06-30 00:00:00.000 test       2010-06-09 14:47:17.163
6           2010-07-01 00:00:00.000 2010-07-01 00:00:00.000 test       2010-06-10 14:49:16.210
7           2010-07-31 00:00:00.000 2010-07-31 00:00:00.000 test       2010-06-09 14:53:16.210

When I tried a query "Select DISTINCT service_id, service_start, service_end, updated_by, last_updated from services order by last_updated desc" gives me all the records.

Thanks for help in advance.
Comment
Watch Question

View Solution Only

Lowfatspread
Top Expert 2011

Commented:
Your query is ambiguous....
you need to tell us what are the determining factors for selecting those rows
I'm guessing..
? you want the maximum last updated row for each service start time?

select *
from yourtable as A
Inner join (select service_start,last_updated,max(service_id) as service_id
                    from yourtable
                  group by service_start,last_updated
                 ) as B
 on A.service_id=b.service_id
the following 2 and are  only required if service is not your primary key
and a.service_start=b.service_start
and a.last_updated=b.last_updated

Commented:
try like this

Select
A.service_id,  A.service_start,           A.service_end  ,           A.updated_by, A.last_updated
from tableName A
join (Select service_start, Max(service_id) Max_service_id from tablename group by service_start) B
on A.service_id = Max_service_id