I'm new to CTE's and usually sub query my SQL. I'm unsure how to get the results I want as I keep finding solutions that would require sub queries.

I have the below table in SQL. I'm trying to write a CTE to give me one line of data per day for each person. Where a person has more than one location ('where' field) for a day - I need to exclude all lines for that day. If a person has multiple lines for one day but the location is the same place - I want to only keep one line of this data.

This is an example of the original table:

Original Table

This is an example of the result that I'm looking for (note - I don't need the ID field in the result).

Desired result

Below is the definition and insert statement to the original dataset:

(`ID` int, `Person` varchar(5), `Date` varchar(10), `Where` varchar(6));

(`ID`, `Person`, `Date`, `Where`)
(1, 'Laura', '17/10/2019', 'School'),
(2, 'Laura', '18/10/2019', 'School'),
(3, 'Laura', '19/10/2019', 'School'),
(4, 'Laura', '19/10/2019', 'Park'),
(5, 'Laura', '20/10/2019', 'Home'),
(6, 'Laura', '21/10/2019', 'School'),
(7, 'Laura', '21/10/2019', 'School'),
(8, 'Laura', '21/10/2019', 'Home'),
(9, 'Tom', '17/10/2019', 'Home'),
(10, 'Tom', '18/10/2019', 'Home'),
(11, 'Tom', '18/10/2019', 'Home'),
(12, 'Tom', '19/10/2019', 'Home'),
(13, 'Tom', '19/10/2019', 'School'),
(14, 'Tom', '20/10/2019', 'Home');

Don't know why you need a CTE (or a subselect), wouldn't this simply be

select person, date_, max(where_) from tbl1
group by person, date_
having count(distinct where_) < 2


PS. Note I changed the table definition to 1) remove backticks that are only used by MySQL to quote identifiers (Redshift is based on Postgres) and 2) rename columns with names that conflict with SQL keywords.


I'm not certain this will be copy and pastable to RedShift, but this is how you'd construct the CTE.

The idea is to order the table by person and date, and then every time the person or date changes, the ordering will be reset to 1. If a row exists with an rn > 1, then that means it had more than 1 entry on that person and date, and therefore should be excluded.

The not exist clause handles the exclusion.

select ID, Person, "Date", "Where", DENSE_RANK() OVER (partition by Person, Date order by Person, Date) "rn"
from table
select *
from cte c1
where not exists (select 1 from cte c2 where c1.id = c2.id and c2.rn > 1)
and c1.rn = 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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