By Nrj


2018-12-13 05:15:46 8 Comments

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    

1 comments

@Akina 2018-12-13 05:52:08

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

@Rick James 2018-12-13 18:04:13

While it works, it is Order(N^2), so it does not scale well. That is, don't expect good performance beyond a few thousand rows.

@Akina 2018-12-13 19:00:49

While it works, it is Order(N^2) Some questions. 1) What is N in THAT case? 2) How this formula considered indices? 3) How can iteration terms and methods be applied to group processing?

Related Questions

Sponsored Content

3 Answered Questions

2 Answered Questions

[SOLVED] Select date based on the daylight saving periods

  • 2017-03-30 18:58:17
  • Daniel Oliveira
  • 1713 View
  • 1 Score
  • 2 Answer
  • Tags:   mysql timestamp

1 Answered Questions

[SOLVED] how to sort by timestamp field while counting duplicates

2 Answered Questions

[SOLVED] SQL - Select Rows in a Certain Order

  • 2015-07-06 15:39:59
  • hassansin
  • 585 View
  • 1 Score
  • 2 Answer
  • Tags:   mysql

3 Answered Questions

[SOLVED] Select rows based on latest date with multiple joins

  • 2014-01-12 17:20:47
  • stiq
  • 45412 View
  • 8 Score
  • 3 Answer
  • Tags:   mysql group-by

Sponsored Content