SQL Server 2008: TOP 10 and distinct together
As the title says, I'm using SQL Server 2008. Apologies if this question is very basic. I've only been using SQL for a few days. Right now I have the following query:
SELECT TOP 10 p.id, pl.nm, pl.val, 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
What I want to do is use select top n together with distinct values in the id column. Searching through some forums says to use
SELECT DISTINCT TOP 10 ...
but when I replace the first line with
SELECT DISTINCT TOP 10 p.id, pl.nm, pl.val, pl.txt_val
I get the same results as without the word distinct. What should I be doing to only get to filter out duplicate id entries?
Thank you.
Try
SELECT TOP 10 distinct MyId FROM sometable;
select top 10 p.id from(select distinct p.id from tablename)tablename
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