SQL Server 2008 TOP 10 and distinct together

Question

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

User · Answer

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

User · Answer

select top 10   from       select distinct p id           will work

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

select top 10 p id from select distinct p id  from tablename tablename

User · Answer

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

User · Answer

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

User · Answer

Few ideas    You have quite a few fields in your select statement  Any value being different from another will make that row distinct  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 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

User · Answer

SELECT TOP 14 A  B  C   FROM MyDatabase   Where EXISTS            Select Distinct A  FROM MyDatabase

User · Answer

Try SELECT TOP 10 distinct MyId FROM sometable

User · Answer

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

User · Answer

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

[sql] SQL Server 2008: TOP 10 and distinct together

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008