How to get get Unique Records based on multiple columns from a table

Question

Consider the following table:

primaryKey   id    activity   template  creator   created
1            1      3           5         x       2011-10-13
2            2      4           2         y       2011-10-15  
3            2      4           7         z       2011-10-24
4            2      4           7         u       2011-10-29

From here I want to retrieve the records which are having unique combinations for id, activity and template. In case there are two or more unique combinations of those fields are exists I want to take the first one of them.

As an example for above table data the output that I need is

primaryKey   id    activity   template  creator  created
1            1      3           5         x       2011-10-13
2            2      4           2         y       2011-10-15  
3            2      4           7         z       2011-10-24

(since record 3 and 4 are having same combination I want to take just the record 3 because it is the first occurance)

Can I do this using a single SQL statement?

"first one" - by `primaryKey`, or by `created`, or by some other logic? Tables don't have an order. — Damien_The_Unbeliever, May 30 '12 at 13:05

score 6 · Accepted Answer · answered May 30 '12 at 13:06

6

SELECT primarykey, id, activity, template, creator, created FROM (
    SELECT *, row_number() OVER (partition BY id, activity, template ORDER BY created) as rn FROM table
) a 
WHERE rn = 1

answered May 30 '12 at 13:06

Jonas Lincoln

9,567
9
35
49

2

Posted the same answer 1 minute after yours. Deleted mine and upvoted yours – t-clausen.dk May 30 '12 at 13:10
@JonasLincoln IMHO and according to [This Answer](https://stackoverflow.com/a/4671347/3814721) *The partition function has to be defined on a single column.* I have also tested it for multiple columns. Query worked with no errors but didn't provide desired output. – mmushtaq Jul 21 '17 at 04:42
I'm desperately trying to get this to work, but the first row returned has a row number of 36. What the heck? The remaining two rows are partitioned as I would expect (they're 1 and 2 respectively.) Thoughts? – Marvo Nov 30 '17 at 03:19

CatchingMonkey · Answer 2 · 2012-05-30T13:04:07.220

This is for MS SQL Server.

Updated, as i made a little mistake!

SELECT DISTINCT 
        ROW_NUMBER() OVER (ORDER BY 
                                id    
                            ,   activity   
                            ,   template  
                            ,   creator  
                            ,   created ) PrimaryKey

    ,   id    
    ,   activity   
    ,   template  
    ,   creator  
    ,   created 
    FROM 
    [TABLE_NAME]
    GROUP BY 

        id    
    ,   activity   
    ,   template  
    ,   creator  
    ,   created

Kshitij · Answer 3 · 2012-05-30T13:13:52.443

0

I think this should work -

SELECT * 
FROM TABLE
WHERE 
 primaryKey in 
 (
   SELECT min(primarkyKey) from TABLE 
      group by id, activity, template
 )

Here, first distinct is obtain on required columns in the inner query by doing group by. Then the min of primary key of each distinct record is used to get all the columns from the outer query.

edited May 30 '12 at 13:13

answered May 30 '12 at 13:02

Kshitij

8,474
2
26
34

You don't need distinct in this case. – Gordon Linoff May 30 '12 at 13:12

How to get get Unique Records based on multiple columns from a table

3 Answers3

Linked