title: Select a range of number
body: <p>Query:</p>
  
  <pre><code>SELECT nummer
     FROM tabell_med_nummer
    WHERE SUBSTR(nummer, -1) = 0
  </code></pre>
  
  <p>This query selects a number in the table tabell_med_nummer, that ends with "0". For example it will select 1000, 1010, 1020, but not 1001, 1002 and so on.</p>
  
  <p>I need to get a range based on this. For example 1000-1100, or 1010-1023. The size of the range is variable. What it ends with is not important, but it have to select a number that ends with "0" like 1010.</p>
  
  <p>The tricky part is that not every number exists in the table. So if 1004 doesn't exists in the table, end I need a range on 100, it can't start at 1000.</p>
  
  <p>Does anyone know how I can solve this?</p>
  
  <p>As requested:</p>
  
  <pre><code>    +----+-------------+-------+-------+-------------------------------+---------------+---------+------+------+---------------------------------+
  | id | select_type | table | type  | possible_keys                 | key           | key_len | ref  | rows | Extra                           |
  +----+-------------+-------+-------+-------------------------------+---------------+---------+------+------+---------------------------------+
  |  1 | SIMPLE      | f     | ALL   | NULL                          | NULL          | NULL    | NULL |   19 | Using temporary; Using filesort |
  |  1 | SIMPLE      | t2    | index | telefonnummer,telefonnummer_2 | telefonnummer | 5       | NULL | 1001 | Using index; Using join buffer  |
  |  1 | SIMPLE      | p     | ALL   | NULL                          | NULL          | NULL    | NULL | 4568 | Using where; Using join buffer  |
  |  1 | SIMPLE      | t3    | index | telefonnummer,telefonnummer_2 | telefonnummer | 5       | NULL | 1001 | Using index; Using join buffer  |
  |  1 | SIMPLE      | t1    | ALL   | telefonnummer,telefonnummer_2 | NULL          | NULL    | NULL | 1001 | Using where; Using join buffer  |
  +----+-------------+-------+-------+-------------------------------+---------------+---------+------+------+---------------------------------+
  5 rows in set (0.00 sec)
  </code></pre>
  
  <p>I changed some table and field names and added a few joins and where statments:</p>
  
  <pre><code>SELECT t1.telefonnummer start, t2.telefonnummer end, t1.kundenummer kundenummer, t1.fornavn fornavn, t1.etternavn etternavn, t1.bedriftsnavn bedriftsnavn, t1.organisasjonsnummer organisasjonsnummer, t1.partnerID partnerID
  
  FROM TELEFONNUMMERTILDELING t1
  
  JOIN TELEFONNUMMERTILDELING t2
  ON t1.telefonnummer % 10 = 0
  AND t1.telefonnummer &lt;= t2.telefonnummer
  
  JOIN TELEFONNUMMERTILDELING t3
  ON t3.telefonnummer BETWEEN t1.telefonnummer AND t2.telefonnummer
  
  JOIN TELEFONNUMMERTILDELING_POSTNUMMER p
  ON p.postnummer = 4085
  
  JOIN TELEFONNUMMERTILDELING_FYLKE f
  ON f.ID = p.fylkeID GROUP BY start, end
  
  HAVING end - start + 1 = COUNT(*)
  AND end - start + 1 = 50
  AND (kundenummer IS NULL OR kundenummer = '')
  AND (fornavn IS NULL OR fornavn = '')
  AND (etternavn IS NULL OR etternavn = '')
  AND (bedriftsnavn IS NULL OR bedriftsnavn = '')
  AND (organisasjonsnummer IS NULL OR organisasjonsnummer = '')
  AND partnerID = 1001 
  </code></pre>