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');
  • Sample data in the form of actual insert statements (instead of screenshots) is definitely appreciated. – Scott Hodgin Oct 17 at 20:12
  • Sorry - I don't know how to do that.. I obviously have a lot to learn. – AceC Oct 17 at 20:15
  • Highlight/Copy your sample data from your spreadsheet and head over to sqlfiddle. At the top of the page, click Text To DDL and paste your data and click Append to DDL. Then take the output of that and paste the table definition and insert statements into your question. – Scott Hodgin Oct 17 at 20:20
  • Thank you - that's really useful and will definitely be bookmarked! – AceC Oct 17 at 20:28
  • Cool - I don't know Redshift, so someone else will have to help with the solution. People are more willing to help when they don't have to do too much work (like translating screenshots to inserts) to test things on their side :) – Scott Hodgin Oct 17 at 20:32

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;

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.

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.