Skip to main content
Tweeted twitter.com/StackDBAs/status/923482091366567937
edited tags
Link
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305
added 284 characters in body
Source Link
user3719749
  • 103
  • 1
  • 1
  • 5

I have a table with the following data:

userID  tStamp                status
------  -------------------   ------
Jason   2017-10-18 03:20:00   idle  
Brown   2017-10-18 03:20:28   idle  
Brown   2017-10-18 03:25:28   idle  
Brown   2017-10-18 04:00:28   active    
Brown   2017-10-18 04:10:28   active    
Brown   2017-10-18 04:35:28   idle  
Brown   2017-10-18 04:45:28   idle  

I want to extract the consecutive rows with the same status. For example, I want to see the time spent by a user in the 'idle' status, followed by 'active' status, followed by 'idle' status, and so on.

How do I do this in a single SQL query?

My desired output is the following:

userID        staus          Duration_in_this_status (min)
------  -------------------   ------
Jason         idle             ---  
Brown         idle              5
Brown         active           10   
Brown         idle             10   

I have a table with the following data:

userID  tStamp                status
------  -------------------   ------
Jason   2017-10-18 03:20:00   idle  
Brown   2017-10-18 03:20:28   idle  
Brown   2017-10-18 03:25:28   idle  
Brown   2017-10-18 04:00:28   active    
Brown   2017-10-18 04:10:28   active    
Brown   2017-10-18 04:35:28   idle  
Brown   2017-10-18 04:45:28   idle  

I want to extract the consecutive rows with the same status. For example, I want to see the time spent by a user in the 'idle' status, followed by 'active' status, followed by 'idle' status, and so on.

How do I do this in a single SQL query?

I have a table with the following data:

userID  tStamp                status
------  -------------------   ------
Jason   2017-10-18 03:20:00   idle  
Brown   2017-10-18 03:20:28   idle  
Brown   2017-10-18 03:25:28   idle  
Brown   2017-10-18 04:00:28   active    
Brown   2017-10-18 04:10:28   active    
Brown   2017-10-18 04:35:28   idle  
Brown   2017-10-18 04:45:28   idle  

I want to extract the consecutive rows with the same status. For example, I want to see the time spent by a user in the 'idle' status, followed by 'active' status, followed by 'idle' status, and so on.

How do I do this in a single SQL query?

My desired output is the following:

userID        staus          Duration_in_this_status (min)
------  -------------------   ------
Jason         idle             ---  
Brown         idle              5
Brown         active           10   
Brown         idle             10   
Improved formatting; removed irrelevant tag; rewordings.
Source Link
MDCCL
  • 8.5k
  • 3
  • 31
  • 62

Selecting consecutive"consecutive" rows with same values in mysql table

I have a table with the following structuredata:

userID tStamp status

Jason 2017-10-18 03:20:00 idle
Brown 2017-10-18 03:20:28 idle
Brown 2017-10-18 03:25:28 idle
Brown 2017-10-18 04:00:28 active
Brown 2017-10-18 04:10:28 active
Brown 2017-10-18 04:35:28 idle
Brown 2017-10-18 04:45:28 idle

userID  tStamp                status
------  -------------------   ------
Jason   2017-10-18 03:20:00   idle  
Brown   2017-10-18 03:20:28   idle  
Brown   2017-10-18 03:25:28   idle  
Brown   2017-10-18 04:00:28   active    
Brown   2017-10-18 04:10:28   active    
Brown   2017-10-18 04:35:28   idle  
Brown   2017-10-18 04:45:28   idle  

I want to extract the consecutiveconsecutive rows with the same statusstatus. For example, I want to see the time spent by a useruser in the idle state'idle' status, followed by active state'active' status, followed by idle state'idle' status, and so on. 

How do I do this in a single sqlSQL query?

Selecting consecutive rows with same values in mysql table

I have a table with the following structure:

userID tStamp status

Jason 2017-10-18 03:20:00 idle
Brown 2017-10-18 03:20:28 idle
Brown 2017-10-18 03:25:28 idle
Brown 2017-10-18 04:00:28 active
Brown 2017-10-18 04:10:28 active
Brown 2017-10-18 04:35:28 idle
Brown 2017-10-18 04:45:28 idle

I want to extract the consecutive rows with the same status. For example, I want to see the time spent by a user in the idle state, followed by active state, followed by idle state, and so on. How do I do this in a single sql query?

Selecting "consecutive" rows with same values

I have a table with the following data:

userID  tStamp                status
------  -------------------   ------
Jason   2017-10-18 03:20:00   idle  
Brown   2017-10-18 03:20:28   idle  
Brown   2017-10-18 03:25:28   idle  
Brown   2017-10-18 04:00:28   active    
Brown   2017-10-18 04:10:28   active    
Brown   2017-10-18 04:35:28   idle  
Brown   2017-10-18 04:45:28   idle  

I want to extract the consecutive rows with the same status. For example, I want to see the time spent by a user in the 'idle' status, followed by 'active' status, followed by 'idle' status, and so on. 

How do I do this in a single SQL query?

Source Link
user3719749
  • 103
  • 1
  • 1
  • 5
Loading