How can I select a range of items, after some values using SQL IN operator

Asked

Viewed 103 times

0

For example: I want to fetch, 1,2,5,7,11 and then the rest up to 100, in a DQL SELECT. Ex.:

SELECT column_name(s) FROM table_name WHERE column_name IN (1,2,5,7,11 ... value100);

In case my doubt is in relation to the use of the operator IN It would not be the between

2 answers

4


Assuming Voce has a table like:

CREATE TABLE tb_foobar
(
    id INTEGER PRIMARY KEY,
    value INTEGER
);

Containing the following data:

INSERT INTO tb_foobar ( id, value ) VALUES (  1,   1 );
INSERT INTO tb_foobar ( id, value ) VALUES (  2,   2 );
INSERT INTO tb_foobar ( id, value ) VALUES (  3,   3 );
INSERT INTO tb_foobar ( id, value ) VALUES (  4,   4 );
INSERT INTO tb_foobar ( id, value ) VALUES (  5,   5 );
INSERT INTO tb_foobar ( id, value ) VALUES (  6,   6 );
INSERT INTO tb_foobar ( id, value ) VALUES (  7,   7 );
INSERT INTO tb_foobar ( id, value ) VALUES (  8,   8 );
INSERT INTO tb_foobar ( id, value ) VALUES (  9,   9 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 10,  10 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 11,  11 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 12,  50 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 13,  60 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 14,  80 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 15, 100 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 16, 120 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 17, 200 );

You can do something like:

SELECT
    id,
    value
FROM
    tb_foobar
WHERE
    value IN ( 1, 2, 5, 7 ) OR
    value BETWEEN 11 AND 100;

Exit:

| id | value |
|----|-------|
|  1 |     1 |
|  2 |     2 |
|  5 |     5 |
|  7 |     7 |
| 11 |    11 |
| 12 |    50 |
| 13 |    60 |
| 14 |    80 |
| 15 |   100 |

Sqlfiddle: http://sqlfiddle.com/#! 9/a9d602/1

3

In that case you have to use the BETWEEN:

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value100;

See more about the BETWEEN here.

  • whereas value1 until value100 are all sorted and which are integer, dates, or strings

  • I just edited the question. I had expressed myself wrong.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.