2

I have a table with the following structure. I need to return all rows where the district of the record immediately preceding and immediately following the row are different than the district for that row. Is this possible? I was thinking of a join on the table itself but not sure how to do it.

id   | zip_code | district
__________________________
20063  10169       12
20064  10169        9
20065  10169       12
Brian
  • 201
  • 2
  • 16
  • possible duplicate of [Compare rows in same table in mysql](http://stackoverflow.com/questions/13244136/compare-rows-in-same-table-in-mysql) – Paul Aug 11 '13 at 23:57
  • The id field is the primary field. I may not have explained this well. I want to compare 9 in the district column to the 12 before it and after it. If it is different than either(in this case it is) it wil return that row(row 2064 in this case). – Brian Aug 11 '13 at 23:59

1 Answers1

4

Assuming that "preceding" and "following" are in the sense of the ID column, you can do:

select * 
 from zip_codes z1
    inner join zip_codes z2 on z1.id=z2.id + 1
    inner join zip_codes z3 on z1.id=z3.id - 1
where z1.district <> z2.district and z1.district <> z3.district

This will automatically filter out the first and last rows, because of the inner joins, if you need those to count, change it to left outer join.

Also, this checks if it's different from both. To find if it's different from either (as is implied in the comment), change the and in the where clause to an or. But note, that then, all three rows in your example fit that criteria, even if there are long rows of twelves above and below these rows.

SWeko
  • 30,434
  • 10
  • 71
  • 106
  • It's the inequality operator defined in the SQL standard. Most database servers, however (MySQL included) also implement the `!=` operator with the exact same semantics. – SWeko Aug 12 '13 at 00:09
  • What if there are some primary keys missing in the sequence (e.g. a deleted record)? – Joseph Silber Aug 12 '13 at 00:43
  • @JosephSilber Well, than my assumption is incorrect, and the OP needs another query :) – SWeko Aug 12 '13 at 09:54