SQL Server 2008：TOP 10 和不同的一起

发布于12月18日

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.

推荐答案

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