Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to select * from A A but only where A.attr1 is distinct. How can I do this?

share|improve this question
1  
What RDBMS? Also please clarify. You want rows where the value for the column attr1 does not exist in any other rows in the table? –  Martin Smith Jun 14 '13 at 20:30
3  
Doesn't quite make sense what you're looking for. Can you re-explain, or give us some sample data and desired output? –  Thomas Stringer Jun 14 '13 at 20:37
add comment

closed as not a real question by Mark Storey-Smith, dezso, StanleyJohns, Martin Smith, Jon Seigel Jun 15 '13 at 14:50

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

If you are using sql server 2005 and up, you can use ROW_NUMBER() as described below :

declare @kinTest table (ID int, Name char(15))
insert into @kinTest values (1 ,  'David' )
insert into @kinTest values (2 ,  'Davila' )
insert into @kinTest values (3 ,  'Jones' )
insert into @kinTest values (4 ,  'jonney' )
insert into @kinTest values (1 ,  'David' )
insert into @kinTest values (2 ,  'Davila' )
insert into @kinTest values (3 ,  'Jones' )
insert into @kinTest values (4 ,  'jonney' )



SELECT  *
FROM    (SELECT [id] ,[name],
                ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY ID) AS RowNumber
         FROM   @kinTest
         --WHERE  NAME LIKE 'da%' -- filter condition ...
         ) AS a
WHERE   a.RowNumber = 1

Original table :

enter image description here

Results (distinct selected ) :

enter image description here

share|improve this answer
    
++ to Kin. Note that IBM DB2 also has rownumber() over(). –  Chris Aldrich Jun 14 '13 at 20:54
    
May do what they want. DAVID is in the results but is not DISTINCT in the source data though. Another way it could be interpreted is only the rows where COUNT(*) OVER (PARTITION BY NAME) is 1 –  Martin Smith Jun 14 '13 at 20:55
    
@MartinSmith Good catch ! was going to set id of 'David' as 1, to make it duplicate. Updated my answer :-) –  Kin Jun 14 '13 at 21:12
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.