0

Let's say there is a table like the following: 

id  | number
----|----------
 1  | 1
 4  | 6
 5  | 2
14  | 3

now I need all numbers that are not set between 1 and the highest number that is set. Here the highest number is 6, so I need all numbers that are not set between 1 and 6. Here it is: 4 and 5

But how can I achieve this with spring and hibernate jpa with a MySQL database?
The highest number is easy. Sort numbers DESC and then the first one. But then return all missing numbers that are not in the database? Is this possible?

One way: select each number that is smaller then the highest number and check if the returned object is null. So first check 5, then 4, then 3, ... But this is of course very slow on big databases.

So another idea was: get all numbers that are set and get the missing numbers on java side with the difference of two lists (one list with the numbers out of the database, the other list with the numbers from 1 to the highest number of the database). But on big databases it is also dumb to get everything. (Let's say, there are 1 million entries and only one number is missing.)

The third idea: something like select where number NULL would be perfect. But for this the database would have to be initialized with all possible numbers ever there. So that is also not possible.

Is there a possible way? Am I overseeing something?

toddeTV
  • 1,447
  • 3
  • 20
  • 36
  • 3
    This is rather a question of SQL, not of spring/hibernate. In **Postgres** , I do it this way: `select * from generate_series(1,(select max(number) from yourtable)) except select number from yourtable;` – Eugene Feb 22 '17 at 00:03
  • Unfortunetely I use MySQL. Is there a equivalent Syntax? – toddeTV Feb 22 '17 at 00:39
  • http://stackoverflow.com/questions/186756/generating-a-range-of-numbers-in-mysql then you can use the range and LEFT JOIN with your table leaving nulls. – StanislavL Feb 22 '17 at 08:04
  • Creating a second table with all numbers in oder to join them (outer join) also was one of my ideas. But I am still hoping there is a better solution for this... – toddeTV Feb 22 '17 at 10:04

0 Answers0