Joolio Joolio - 5 months ago 7
SQL Question

SQL selection query that discards rows that share some value with another row

Suppose I have the next SQL table, with these sample values:

ID Date User Publish
----------------------------------
1 05/20/16 Peter 1
2 05/20/16 Peter 2 <= Discarded
3 05/20/16 John 2
4 05/28/16 John 1
5 05/28/16 John 2 <= Discarded
6 07/01/16 Peter 2
7 07/01/16 John 2


What I want to get is a query to select all rows but in case there are two rows for the same date and user, retrieve the one with 'Publish' value to '1', so in the example I get the rows 1, 3, 4, 6 and 7.

I resolve the problem programmatically but I wonder if exists a way to solve it with a proper sql query.

Any help will be appreciated.

MT0 MT0
Answer

This will discard all but the first row (ordered by publish) for each group of date and user:

SELECT ID, "Date", User, Publish
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY "Date", User ORDER BY Publish ) AS rn
  FROM   table_name t
)
WHERE rn = 1;