MySQL SELECT row if next row is different value?

Question

I've Googled a lot but I can't quite figure this out. I apologize if this is a trivial question.

I would like to SELECT a row if the 'batter' column equals a certain value AND the next row is not the same batter. I'm trying to get the row that represents the final pitch of the at bat. If it is the final pitch, then the next row would be a new batter. I'm using an auto-incremented id column if that helps.

EDIT:

Here's how the data is organized. Right now if I want to know how many times batter "276055" struck out I would do:

SELECT * FROM `mlb2012` WHERE batter = "276055" AND atbat_event = "strikeout" AND atbat_pitch = "1"

But if I want to know how many times he struck out looking, I would need to know the pitch_des of the last pitch of the atbat.

ID | Batter | atbat_pitch | atbat_event | pitch_des      | 
----------------------------------------------------------
1  | 457477 | 1           | Double      | Called Strike
2  | 457477 | 2           | Double      | In play, no out
3  | 452121 | 1           | Strikeout   | Foul
4  | 452121 | 2           | Strikeout   | Foul
5  | 452121 | 3           | Strikeout   | Called Strike
6  | 543569 | 1           | Walk        | Ball

show some queries you have now, and how the data is structured in your database — Rene Pot
– Rene Pot, Commented Nov 7, 2012 at 19:14
this is a LEAD or LAG windowed function - might help your searching. — Randy
– Randy, Commented Nov 7, 2012 at 19:37

Alain Collins · Accepted Answer · 2012-11-07 19:16:09Z

3

Pseduo code (90% likely to work):

set @last_batter_id = NULL;

select batter_id, @last_batter_id,
  case when batter = "certain value" and batter_id != @last_batter_id then 'use me' else 'skip me' end as my_action,
  @last_batter_id := batter_id
from my_table

If you want just the batters that you want to use, wrap the last query:

select *
from (
   [ query from above ]
) foo
where foo.my_action = 'use me'

answered Nov 7, 2012 at 19:16

Alain Collins

16.4k2 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1807053 Over a year ago

I'm a little confused on this. What are the 'use me' and 'skip me' values? Thank you.

Alain Collins Over a year ago

The first query makes the decision as to whether it's a new batter with the certain value. It needs to tell you that, so when it returns "use me", it would be a row you want to use, and when it returns "skip me", it would be a row that you want to skip. You can wrap the first query with the second to just look at the ones you want to use.

Alex · Accepted Answer · 2012-11-07 19:34:10Z

0

Intriguing question.

Looks like you want to join that table to itself based on the rowid on the current row joining the rowid on the previous and checking the 'batter' field is not equal. Use left outer join to handle the last row (ie will return if there is no subsequent row to be joined to). Dont' know mysql syntax but try something like:

SELECT fr.* 
FROM mytable fr 
LEFT OUTER JOIN mytable nr 
ON fr.@rowid = nr.@rowid+1 
WHERE fr.batter <> nr.batter

(you can replace the left outer join and on clauses with where conditions and += arguments - I assume you've got the basic MySQL syntax down).

answered Nov 7, 2012 at 19:34

Alex

68011 silver badges21 bronze badges

1 Comment

user1807053 Over a year ago

SELECT fr. * FROM mlb2012 fr LEFT OUTER JOIN mlb2012 nr ON fr.id = nr.id +1 WHERE fr.batter <> nr.batter I tried the above. It gave me the first pitch of every atbat, so it's close. I need to read to figure out what exactly it's doing, but thank you. Now I need to get it to show the rows of the last pitch.

Collectives™ on Stack Overflow

MySQL SELECT row if next row is different value?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related