MySQL – Selecting Rows Based on Specific User ID Conditions

google-bigquerygreatest-n-per-groupMySQL

Below is an example of child's activity table. I only want to see all rows after 1st Tv activity for each user id. I tried grouping but it is not working for me

Table

ID          Timestamp               Activity
567     1541300537817000            Games
567     1541300584959000            Tv
567     1541300711884001            Play
567     1541300749548000            Tv
567     1541300804865000            Cartoon
678     1542073550481000            Computer
678     1542073551422000            Tv
678     1542073551478000            Study
678     1542073561885000            Tv
678     1542073567663000            Sleep     
908     1543580126943000            Tv     
908     1543580212409000            Sleep     
908     1543580245227000            Play

Expected Output 

567  Play  
567  Tv  
567  Cartoon    
678  Study  
678  Tv  
678  Sleep   
908  Sleep       
908  Play

Best Answer

SELECT DISTINCT t1.*
FROM `table` t1, `table` t2
WHERE t1.id = t2.id
  AND t1.timestamp > t2.timestamp
  AND t2.activity = 'Tv';
Related Question