SQL Server 2008: TOP 10 and distinct together
The easy option is to use group by and select min/max for all other fields
SELECT TOP 10
p.id,
max(pl.nm),
max(pl.val),
max(pl.txt_val)
from
dm.labs pl
join
mas_data.patients p
on
pl.id = p.id
where
pl.nm like '%LDL%'
and
val is not null
group by
p.id
This can get quite tedious for wide table so the other option is to use rank over and partiion
SELECT TOP 10
p.id,
pl.nm,
pl.val,
pl.txt_val,
rank() over(partition by p.id order by p.id) as Rank
from
dm.labs pl
join
mas_data.patients p
on
pl.id = p.id
where
pl.nm like '%LDL%'
and
val is not null
and
Rank = 1
select top 10 p.id from(select distinct p.id from tablename)tablename
Try
SELECT TOP 10 distinct MyId FROM sometable;