0

Does anyone happen to know a way of basically taking the 'Distinct' command but only using it on a single column. For lack of example, something similar to this:

        Select (Distinct ID), Name, Term from Table

So it would get rid of row with duplicate ID's but still use the other column information. I would use distinct on the full query but the rows are all different due to certain columns data set. And I would need to output only the top most term between the two duplicates:

ID  Name    Term
1   Suzy    A
1   Suzy    B
2   John    A
2   John    B
3   Pete    A
4   Carl    A
5   Sally   B

Any suggestions would be helpful.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Walcottjc
  • 31
  • 1
  • 3
  • 1
    What DBMS are you using? – user2989408 Jun 23 '14 at 16:57
  • possbile duplicate http://stackoverflow.com/questions/251278/select-one-column-distinct-sql – Harry Jun 23 '14 at 16:57
  • Try `SELECT ID, MIN(Name), MIN(Term) FROM Table GROUP BY ID`. – user2989408 Jun 23 '14 at 16:58
  • So suppose you have id = 1 and id =2, name = Suzy, name = Suzy, and Term = A and Term = B. (eg {1,Suzy,A}, {2, Suzy, B}). You want to keep only one id, the one whose id occurs first, and still show two rows. Why not just use MIN(Id) AS ID and group by name and term? – Evan Volgas Jun 23 '14 at 16:58
  • Yes correct. I would only want to Keep {1, Suzy, A} in that case. I am using SQL Server 2008 R2 to answer the first guys question. I will try this Min() solution you both suggested. Thanks – Walcottjc Jun 23 '14 at 18:11
  • Using the Min aggregate and Group by worked great, I was able to accomplish the output I wanted. Thank You! – Walcottjc Jun 23 '14 at 18:23

2 Answers2

0
select t.Id, t.Name, t.Term
from (select distinct ID from Table order by id, term) t
Horaciux
  • 6,322
  • 2
  • 22
  • 41
0

You can use row number for this

Select ID, Name, Term from(
   Select ID, Name, Term,  ROW_NUMBER ( ) 
    OVER ( PARTITION BY ID order by Name) as rn from Table
Where rn = 1) 
as tbl

Order by determines the order from which the first row will be picked.

Satyajit
  • 2,150
  • 2
  • 15
  • 28