How to select a range of my table

Question

I have a table like this: (Pay attention to the sequence numbers for every word)

// tablename
+---------+----------+
|  word   |  number  |
----------------------
|  jack   |     1    |
|  jack   |     2    |
|  jack   |     3    |
|  ali    |     1    |
|  ali    |     2    |
|  ali    |     3    |
|  ali    |     4    |
|  ali    |     5    |
|  peter  |     1    |
|  peter  |     2    |
|  peter  |     3    |
|  peter  |     4    |
|  raj    |     1    |
|  raj    |     2    |
+---------+----------+

Now i need a query to select a range. My range is (for example):

// Range Start
select * from tablename where word='ali' and number='2';

// Range End
select * from tablename where word='peter' and number='3';

In fact I want below result:

+---------+----------+
|  word   |  number  |
----------------------
|  ali    |     2    |
|  ali    |     3    |
|  ali    |     4    |
|  ali    |     5    |
|  peter  |     1    |
|  peter  |     2    |
|  peter  |     3    |
+---------+----------+

How can I do that ?

Answer 1

I have to head out but if your data looks like this with the ID column:

+---------+----------+----+
|  word   |  number  | ID |
---------------------------
|  jack   |     1    | 1  |
|  jack   |     2    | 2  |
|  jack   |     3    | 3  |
|  ali    |     1    | 4  |
|  ali    |     2    | 5  |
|  ali    |     3    | 6  |
|  ali    |     4    | 7  |
|  ali    |     5    | 8  |
|  peter  |     1    | 9  |
|  peter  |     2    | 10 |
|  peter  |     3    | 11 |
|  peter  |     4    | 12 |
|  raj    |     1    | 13 |
|  raj    |     2    | 14 |
+---------+----------+----+

Then you could do this (this is just one way, there are most likely more optimal ways)

select *
from table
where id >= (select id from table where word='ali' and number='2')
 and id <= (select id from table where word='peter' and number='3')

Answer 2

Try this:

select * from table 
where (word = 'ali' and number >='2') or (word = 'peter' and number <= '3')
order by word, number

Answer 3

select * from tablename
where (word = 'ali' and number between '2' and '5') OR
      (word = 'peter' and number between '1' and'3')
order by word, number

Answer 4

Assuming you want your data ordered first by word, then by number, you could do something like this:

select * from table
    where strcmp(concat(word, lpad(number, 5, '0')), concat('ali', lpad(2, 5, '0'))) >= 0
    and strcmp(concat(word, lpad(number, 5, '0')), concat('peter', lpad(3, 5, '0'))) <= 0
    order by word asc, number asc;

This takes the two fields, and makes one ASCII string, nameNNNNN, to compare with. This will work independent of the sequencing of the record IDs. If your word and number were set up as a composite index, you could order based upon that and not use the string comparisons and concatenations.

Answer 5

If you have an Id column, this is a simple way to do what you need

SELECT
  word, number 
FROM 
  Test 
WHERE 
  id BETWEEN
    (SELECT id FROM Test WHERE word = 'ali' AND number = '2') AND
    (SELECT id FROM Test WHERE word = 'peter' AND number = '3');

Here you have a working example in SQLFiddle

Hope this helps

Answer 6

    select * from tablename where 
(word = 'ali' and number >= 2) or 
(word = 'peter' and number <= 3);

that should yield the result you are looking for, though if you had a primary key that was unique within the table that served as an index you could simply query the table using something like this;

select * from tablename where index between x and y;

that would obviously suggest that you knew the index values that you needed, though you could get those using some nested conditions like this;

    select * from tablename where index between 
(select index from tablename where name = 'ali' and number =2) and 
(select index from tablename where name=peter and number = 5);

hope that helps.