Having a difficult time putting what I am trying to do into words so searching is also difficult.
Basically I am trying to look whether a certain value exists in a column, partitioned by group, and then propagate that value forward.
In this example I want to check whether a user has completed the tutorial and set a flag that carries forward.
pk | user | | activity 1 | A | | "login" 2 | A | | "started_tutorial" 3 | A | | "completed_tutorial" 4 | A | | "some other activity" 5 | A | | "logout" 5 | B | | "login" 6 | B | | "logout"I think this should be something like
select *, check(activity in ('completed_tutorial')) as completed_activity from tblbut I don’t think I can use check in a select statement and this would be a constant flag rather than set to true only after it has been found.
Example of what I am trying to get:
pk | user | | activity | completed_tutorial 1 | A | | "login" | 0 2 | A | | "started_tutorial" | 0 3 | A | | "completed_tutorial" | 1 4 | A | | "some other activity" | 1 5 | A | | "logout" | 1 5 | B | | "login" | 0 6 | B | | "logout" | 0Advertisement
Answer
You can filter SQL groups with the HAVING clause. For example, you can group your table by users and their activity, and then filter it to contain only those that have completed the tutorial:
SELECT user FROM tblGROUP BY user, activityHAVING activity = 'completed_tutorial';EDIT: After OP has edited their question, this is my new answer. Here, I assume that your table has a date field.
SELECT *, COALESCE(date >= ( SELECT date FROM tbl WHERE activity = 'completed_tutorial' AND user = outertbl.user), FALSE)FROM tbl AS outertblORDER BY dateNotice, that such query is essentially N² when unoptimised, so I would recommend instead to just get the data from the database and then process it in your program.