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

---

Few ideas:

1. You have quite a few fields in your select statement. Any value being different from another will make that row distinct.
2. TOP clauses are usually paired with WHERE clauses. Otherwise TOP doesn't mean much. Top of what? The way you specify "top of what" is to sort by using WHERE
3. It's entirely possible to get the same results even though you use TOP and DISTINCT and WHERE. Check to make sure that the data you're querying is indeed capable of being filtered and ordered in the manner you expect.

Try something like this:

SELECT DISTINCT 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
ORDER BY pl.nm

Note that i commented out some of the SELECT to limit your result set and DISTINCT logic.

---

select top 10 * from
(
    select distinct p.id, ....
)

will work.

---

I know this thread is old, but figured I would throw in what came up with since I just ran into this same issue. It may not be efficient, but I believe it gets the job done.

SELECT TOP 10 p.id, pl.nm, pl.val, pl.txt_val
INTO #yourTempTable
from dm.labs pl 
join mas_data.patients p on pl.id = p.id   
where pl.nm like '%LDL%' and val is not null

select p.id, pl.nm, pl.val, pl.txt_val
from #yourTempTable
where id IN (select distinct id from #yourTempTable)

---

well I wouldn't have expected it, but Halim's SELECT distinct TOP 10 MyId FROM sometable

is functionally identical to Vaishnavi Kumar's select top 10 p.id from(select distinct p.id from tablename)tablename

create table #names ([name] varchar(10))
insert into #names ([name]) values ('jim')
insert into #names ([name]) values ('jim')
insert into #names ([name]) values ('bob')
insert into #names ([name]) values ('mary')
insert into #names ([name]) values ('bob')
insert into #names ([name]) values ('mary')
insert into #names ([name]) values ('john')
insert into #names ([name]) values ('mark')
insert into #names ([name]) values ('matthew')
insert into #names ([name]) values ('luke')
insert into #names ([name]) values ('peter')

select distinct top 5 [name] from #names

select top 5 * from (select distinct [name] from #names) subquery 

drop table #names

produces the same results for both selects:

    name
1   bob
2   jim
3   john
4   luke
5   mark

it's curious that select top 5 distinct is not valid, but select distinct top 5 is and works as you might expect select top 5 distinct to work.

---

DISTINCT removes rows if all selected values are equal. Apparently, you have entries with the same p.id but with different pl.nm (or pl.val or pl.txt_val). The answer to your question depends on which one of these values you want to show in the one row with your p.id (the first? the smallest? any?).

---

I think the problem is that you want one result for each p.id?

But you are getting "duplicate" results for some p.id's, is that right?

The DISTINCT keyword applies to the entire result set, so applies to pl.nm, pl.val, pl.txt_val, not just p.id.

You need something like

SELECT TOP 10 p.id, max( p1.nm ), max (p1.val), ...
FROM ...
GROUP BY p.id

Won't need the distinct keyword then.

---

You could use a Common Table Expression to get the top 10 distinct ID's and then join those to the rest of your data:

;WITH TopTenIDs AS
( 
   SELECT DISTINCT TOP 10 id
   FROM dm.labs 
   ORDER BY ......
)
SELECT 
    tti.id, pl.nm, pl.val, pl.txt_val
FROM
    TopTenIDs tti
INNER JOIN
    dm.labs pl ON pl.id = tti.id
INNER JOIN 
    mas_data.patients p ON pl.id = p.id
WHERE
    pl.nm like '%LDL%'
    AND val IS NOT NULL

That should work. Mind you: if you have a "TOP x" clause, you typically also need an ORDER BY clause - if you want the TOP 10, you need to tell the system in what order that "TOP" is.

PS: why do you even join the "patients" table, if you never select any fields from it??

---

SELECT TOP 14 A, B, C
  FROM MyDatabase
  Where EXISTS 
   (
     Select Distinct[A] FROM MyDatabase
    )

---

This is the right answer and you can find 3 heights value from table

SELECT TOP(1)  T.id FROM (SELECT DISTINCT TOP(3) st.id  FROM Table1 AS t1 , Table2 AS t2 WHERE t1.id=t2.id ORDER BY (t2.id) DESC ) T ORDER BY(T.id) ASC

---

SELECT DISTINCT * FROM (

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

)