How to select all columns in table with Distinct on multiple columns

Question

I've seen some posts similar to this, but nothing I have found really makes sense or works that I have tried. Essentially I need to select distinct on 3 columns, but return all the columns in the table. So far this is what I have:

SELECT *
FROM tbl1
WHERE tblKey IN (SELECT DISTINCT personKey, contentkey, EmailKey
                                FROM tbl1
                                WHERE (Application = 'website' OR Application = 
                                'connect') AND
                                (contentKey IN (12, 13, 14, 16, 17, 18 , 19)) AND
                                (channelKey = 1))

The where statement in the subquery is because This only should apply to the content that is true to the where clause (obviously, but just making sure its known that Im not trying to remove duplicates in the where clause). This query obviously doesnt work since the tblKey is not in the subquery, but I cant figure out a way to work around this.

Sample data and desired results would help.

Gordon Linoff
– Gordon Linoff

2020-07-16 15:43:45 +00:00
Commented Jul 16, 2020 at 15:43 — Gordon Linoff
– Gordon Linoff, Commented Jul 16, 2020 at 15:43

Gordon Linoff · Accepted Answer · 2020-07-16 15:44:48Z

1

If you want one sample row for each combination of keys, then your code would work with aggregation functions:

SELECT *
FROM tbl1
WHERE tblKey IN (SELECT MAX(tblKey) 
                 FROM tbl1
                 WHERE Application IN ('website', 'connect') AND
                       contentKey IN (12, 13, 14, 16, 17, 18 , 19) AND
                       channelKey = 1
                 GROUP BY personKey, contentkey, EmailKey
                );

I do think that it would be more common to express this using window functions:

SELECT t.* 
FROM (SELECT t.*
             ROW_NUMBER() OVER (PARTITION BY personKey, contentkey, EmailKey ORDER BY (SELECT NULL)) as seqnum
      FROM tbl1 t
      WHERE Application IN ('website', 'connect') AND
           contentKey IN (12, 13, 14, 16, 17, 18 , 19) AND
           channelKey = 1
     ) t
WHERE seqnum = 1;

answered Jul 16, 2020 at 15:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to select all columns in table with Distinct on multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related