CODEWITHSUNDEEP
mysqlsql
user1759708
user1759708

Reputation: 157

Select a range of number

Query:

SELECT nummer
   FROM tabell_med_nummer
  WHERE SUBSTR(nummer, -1) = 0

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.

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.

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.

Does anyone know how I can solve this?

As requested:

    +----+-------------+-------+-------+-------------------------------+---------------+---------+------+------+---------------------------------+
| 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)

I changed some table and field names and added a few joins and where statments:

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 <= 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

Upvotes: 0

Views: 1672

Answers (2)

eggyal
eggyal

Reputation: 125835

-- get the start and end points
SELECT   t1.nummer AS start, t2.nummer AS end

-- pair every possible start number with every potential end number
FROM     tabell_med_nummer t1
    JOIN tabell_med_nummer t2
      ON t1.nummer % 10 = 0 AND t1.nummer <= t2.nummer

-- obtain every number in between
    JOIN tabell_med_nummer t3
      ON t3.nummer BETWEEN t1.nummer AND t2.nummer

-- group into potential ranges
GROUP BY start, end

-- now limit only to contiguous ranges
  HAVING end - start + 1 = COUNT(*)

-- and those that contain the desired number of records
     AND end - start + 1 = ?

If nummer is not guaranteed to be unique, e.g. with a UNIQUE constraint, then you will need to replace COUNT(*) with the less performant COUNT(DISTINCT t3.nummer) or else replace t3 with (SELECT DISTINCT nummer FROM tabell_med_nummer).

Upvotes: 2

John Woo
John Woo

Reputation: 263693

add another condition in your WHERE clause.

 SELECT nummer
   FROM tabell_med_nummer
  WHERE SUBSTR(nummer, -1) = 0 AND
        nummer BETWEEN 1000 AND 1100

Upvotes: 1

Related Questions