none
How to select unique rows from SQL table without using DISTINCT?

    Question

  • Hi,

    I need suggestion to select unique rows from DB table without using the keyword 'DISTINCT'.

     


    Thanks and Regards, Bharath S.
    • Changed type Bharath S Tuesday, May 24, 2011 6:50 AM
    Tuesday, May 24, 2011 6:50 AM

Answers

  • ;WITH CTE (Col1,Col2,Col3,DuplicateCount)
    AS
    (
    SELECT Col1,Col2,Col3,
    ROW_NUMBER() OVER(PARTITION BY Col1,Col2,Col3 ORDER BY Col1) AS DuplicateCount
    FROM MyTable)
    Select * from CTE Where DuplicateCount=1

    If this answer is helpful to you .. Please mark as Answer....
    • Proposed as answer by vishu2011 Tuesday, May 24, 2011 7:22 AM
    • Unproposed as answer by vishu2011 Tuesday, May 24, 2011 7:22 AM
    • Proposed as answer by vishu2011 Tuesday, May 24, 2011 7:22 AM
    • Marked as answer by Kalman TothModerator Sunday, May 29, 2011 2:56 AM
    Tuesday, May 24, 2011 6:59 AM

All replies

  • Hi,

    use row_number().

    Similar to this

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/6582bb10-57d8-42c6-9633-f9c64e37d942


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Tuesday, May 24, 2011 6:56 AM
  • ;WITH CTE (Col1,Col2,Col3,DuplicateCount)
    AS
    (
    SELECT Col1,Col2,Col3,
    ROW_NUMBER() OVER(PARTITION BY Col1,Col2,Col3 ORDER BY Col1) AS DuplicateCount
    FROM MyTable)
    Select * from CTE Where DuplicateCount=1

    If this answer is helpful to you .. Please mark as Answer....
    • Proposed as answer by vishu2011 Tuesday, May 24, 2011 7:22 AM
    • Unproposed as answer by vishu2011 Tuesday, May 24, 2011 7:22 AM
    • Proposed as answer by vishu2011 Tuesday, May 24, 2011 7:22 AM
    • Marked as answer by Kalman TothModerator Sunday, May 29, 2011 2:56 AM
    Tuesday, May 24, 2011 6:59 AM
  • My table structure is

    Col1  Col2 COl3

    1         1   data value one                                   
    1         2  data value two                                   
    1          1  data value one                                   
    1         1  data value one                                   
    1         2  data value two                                   

     

    In the above table, how to delete the duplicate rows?...


    Thanks and Regards, Bharath S.
    Tuesday, May 24, 2011 7:13 AM
  • Select without using Distinct

     

    Declare @t Table (Nm varchar(10),Phone varchar(10))
    Insert into @t
    Select 'AA','1111'
    Union All
    Select 'AA','1111'
    Union all
    Select 'BB','9999'
    Union All
    Select 'CCC','12345'
    Union all
    Select 'CCC','12345'

    Select Nm,Phone from @t Group by Nm,Phone


    If this answer is helpful to you .. Please mark as Answer....
    Tuesday, May 24, 2011 7:22 AM
  • Declare @Table1 Table
    (
    	Col1 int,Col2 int,COl3 varchar(100)
    )
    insert into @Table1
    Select 1,1,'data value one' Union ALL
    Select 1,2,'data value two' Union ALL
    Select 1,1,'data value one' Union ALL
    Select 1,1,'data value one' Union ALL
    Select 1,2,'data value two'
    
    --Distinct records without deleting any record
    ;WITH CTE AS
    (
    	Select Col1,Col2,COl3,
    	ROW_NUMBER()Over(PARTITION BY Col2 Order by Col2) As Rn
    	From @Table1
    )
    Select Col1,Col2,COl3
    From CTE where Rn=1
    
    --or delete duplicate records
    Declare @Table Table
    (
    	Col1 int,Col2 int,COl3 varchar(100)
    )
    insert into @Table
    Select 1,1,'data value one' Union ALL
    Select 1,2,'data value two' Union ALL
    Select 1,1,'data value one' Union ALL
    Select 1,1,'data value one' Union ALL
    Select 1,2,'data value two'
    
    --before deleting duplicate record
    Select * From @Table
    
    --delete duplicate record
    ;WITH CTE AS
    (
    	Select Col1,Col2,COl3,
    	ROW_NUMBER()Over(PARTITION BY Col2 Order by Col2) As Rn
    	From @Table
    )
    Delete From CTE where Rn>1
    
    --after deleting duplicate records
    Select * From @Table

    Tuesday, May 24, 2011 7:22 AM

  • HI Bharath!

    You may also use the below query to get the desired result.

    CREATE TABLE #Temp(Col1 INT, Col2 INT, Col3 VARCHAR(MAX))
    
    INSERT INTO #Temp
    SELECT 1,1, 'Value One' UNION ALL
    SELECT 1,2, 'Value Two' UNION ALL
    SELECT 1,1, 'Value One' UNION ALL
    SELECT 1,1, 'Value One' UNION ALL
    SELECT 1,2, 'Value Two'
    
    --1) GROUP BY Approach
    SELECT Col1 , Col2 , Col3 
    FROM #Temp
    GROUP BY Col1 , Col2 , Col3
    
    --2) Row_Number() Approach
    ;WITH CTE AS 
    (
    SELECT Col1 , Col2 , Col3 , ROW_NUMBER() OVER (PARTITION BY Col1 , Col2 , Col3 ORDER BY Col1) AS Cnt
    FROM #Temp)
    SELECT Col1 , Col2 , Col3 FROM CTE WHERE Cnt = 1
    
    --3) DISTINCT Approach
    SELECT DISTINCT Col1 , Col2 , Col3
    FROM #Temp
    
    
    Please let me know if this doesn’t work for you. Hope I have answered you correctly
    
    Thanks, Hasham
    

    Tuesday, May 24, 2011 9:32 AM
  • Hi Hasham,

    Thanks for your reply.

    Shall we use the 2nd option in DB2?...


    Thanks and Regards, Bharath S.
    Tuesday, May 24, 2011 11:15 AM
  • Hi Bharath !

    I am not sure about it if its works in DB2 or not. But may be you might look into this;

    http://www.ibm.com/developerworks/data/library/techarticle/dm-0401kuznetsov/index.html

    Thanks, Hasham

     

    Tuesday, May 24, 2011 12:31 PM
  • Why? What is the problem with DISTINCT?

    Or is this just a quiz? Because in that case, here is another (rather silly) solution:

     

    SELECT my_col FROM my_table
    UNION
    SELECT my_col FROM my_table
    
    
    

     


    Gert-Jan
    Tuesday, May 24, 2011 5:47 PM
  • I think option with GROUP BY should work in most databases. The ROW_NUMBER() version is T-SQL specific.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, May 24, 2011 5:50 PM