How does one detect a non-contiguous number column in a MySQL query?

Question

I can do this in script, but I know this must be easily possible in MySQL. You know how a bank statement will put a little asterisk next to checks whose numbers are out of order? This is exactly what I want to be able to do.

Assuming a table with the usual id pkey but also a checkno integer. I want to return three columns: checkno, id, and outoforder which will be either a blank string or an asterisk:

SELECT t1.checkno, t1.id, outoforder '' FROM table t1 ORDER BY t1.checkno

How would I modify this SQL to do that? I'm thinking a subquery like (SELECT t2.id FROM table t2 WHERE t2.checkno = t1.checkno - 1) but with some MySQL function thrown on top. Incidentally I tried this type of subquery on my actual table and the query just never returns.

you basically need to give the query a "memory" of what came in the previous row. it's doable, but makes the query ugly. you'd be better off doing it in your client-side code. — Marc B, Mar 06 '14 at 21:36
Something like [RANKing](http://stackoverflow.com/questions/3333665/mysql-rank-function) might help you achieve this. — user2989408, Mar 06 '14 at 21:44
sqlfiddle.com some data and show expected result, i am sure you could do it with Ranking like @user2989408 has mentioned — Tin Tran, Mar 06 '14 at 21:48
@Niel Check out this [SQLFiddle](http://sqlfiddle.com/#!2/ae37e0/5). Something like this may work for you. — user2989408, Mar 06 '14 at 21:59
The SQLFiddle mentioned almost works but when I change the order by to `checkno` and insert a gap in those numbers then all the entries following that gap are marked as out of order (instead of just the first one of the gap): `SELECT t1.checkno, t1.id, @curRank := @curRank + 1 AS Rank, CASE WHEN t1.Checkno = @curRank THEN '' ELSE '*' END AS OutOfOrder FROM Tab t1, (SELECT @curRank := (SELECT @curRank := 0)) r ORDER BY t1.checkno;` — Neil C. Obremski, Mar 06 '14 at 22:45

score 1 · Accepted Answer · edited Mar 26 '14 at 03:01

1

by using a variable like this maybe?

SELECT t1.checkno,t1.id,
       IF(@lastCheckNo IS NULL OR @lastCheckNo = t1.checkno - 1,'','*') as outoforder,
       @lastCheckNo := t1.checkno
FROM (SELECT * FROM `table` ORDER BY checkno) t1,(SELECT @lastCheckNo := NULL)variable

sqlFiddle

edited Mar 26 '14 at 03:01

Neil C. Obremski

18,696
24
83
112

answered Mar 06 '14 at 22:22

Tin Tran

6,194
3
19
34

Thanks Tin Tran! Clean, understandable, and I adapted this to my table and it created the same output as my script. – Neil C. Obremski Mar 06 '14 at 22:48

planestepper · Answer 2 · 2014-03-06T23:07:41.710

Should have poor performance, but it's able to handle row ids that are not separated by a single unit (no -1 in the formula):

SELECT
  T.id, T.checkno,
  /* T1.id AS PREVIOUS_ID, T1.checkno AS PREVIOUS_CHECK, */
  /* T2.id AS NEXT_ID, T2.checkno AS NEXT_CHECK, */
  CASE
    WHEN T1.checkno > T.checkno THEN '*'
    WHEN T2.checkno < T.checkno THEN '*'
    ELSE ''
  END AS OUT_OF_ORDER
FROM Tab T
  LEFT JOIN Tab T1 ON T1.id = (SELECT MAX(S1.id) FROM Tab S1 WHERE T.id > S1.id)
  LEFT JOIN Tab T2 ON T2.id = (SELECT MIN(S2.id) FROM Tab S2 WHERE T.id < S2.id)

Have a fiddle. Left the commented code as explanation.

How does one detect a non-contiguous number column in a MySQL query?

2 Answers2