How to check missing number in some column

Question

I use Mysql and spring data jpa

I Have Questions about how to find missing data some column

that column attribute is int.

So.if i insert some data in some data column.

number table

seq | number | sometext | someData | something | some | some

when i insert data like this.

1 0 sometext someData  something some some,

2 2 sometext someData  something some some,

3 3 sometext someData  something some some,

4 5 sometext someData  something some some,

5 10 sometext someData  something some some,

how to get i missing number column

in spring data jpa native query.

what number is missing in your example? – Alain Vanderbroeck Aug 22 '19 at 07:35 — Alain Vanderbroeck, Aug 22 '19 at 07:35

score 1 · Accepted Answer · answered Aug 23 '19 at 01:12

1

How about this query

SELECT   CONCAT(X.EXPECTED, IF(X.GOT-1>X.EXPECTED, CONCAT(' ~ ',X.GOT-1), '')) AS MISSING
FROM     (
         SELECT
                  @ROWNUM:=@ROWNUM+1 AS EXPECTED,
                  IF(@ROWNUM=B.number , 0, @ROWNUM:=B.number ) AS GOT
         FROM     (SELECT @ROWNUM:=0) AS A
                  INNER JOIN YOUR_TABELE B
         ORDER BY B.number 
         ) AS X
WHERE X.GOT!=0;

answered Aug 23 '19 at 01:12

joshua.kim

373
3
11

Thank you sour answer But mysql can this query but i can't use jpa native Query Do you know how to use that Queryuu in spring data jpa – jjosjoahu Aug 23 '19 at 01:34
Well I don't know how to use jpa native query. sorry – joshua.kim Aug 23 '19 at 01:37
maybe use this @ROWNUM \\:= – joshua.kim Aug 23 '19 at 02:19
there ar occur error could not execute query; And why jpa find Column 'sequence' not found – jjosjoahu Aug 23 '19 at 06:52

How to check missing number in some column

1 Answers1