Sql Query For my case

Question

I Have table in format mentioned below

USER_ID ORDER

1        0
1        1
1        2
1        3
2        0
2        1
2        3
3        0
3        2

I want to find list of user id whose ordering is not continious. i.e userid2(have no ordering of 2) and userid3(having no ordering of 1).

Please help

We're not here to do your work for you. What have you tried so far? — Barmar, Jul 19 '13 at 05:47

score 1 · Accepted Answer · answered Jul 19 '13 at 05:50

Assuming there can't be any duplicate order values for a user_id, this should work:

SELECT user_id, max(`order`)-min(`order`)+1 order_range_size, count(*) c
GROUP BY user_id
HAVING c != order_range_size

This doesn't tell you which order is missing from the range; I hope that's OK.

1 Answers1