How to select 10 rows below the result returned by the SQL query?

Question

Here is the SQL table:

KEY | NAME    | VALUE  
---------------------
13b | Jeffrey | 23.5  
F48 | Jonas   | 18.2  
2G8 | Debby   | 21.1

Now, if I type:

SELECT * 
  FROM table  
 WHERE VALUE = 23.5

I will get the first row.

What I need to accomplish is to get the first and the next two rows below. Is there a way to do it?

Columns are not sorted and WHERE condition doesn't participate in the selection of the rows, except for the first one. I just need the two additional rows below the returned one - the ones that were entered after the one which has been returned by the SELECT query.

Answer 1

If I understand correctly, you're looking for something like: SELECT * FROM table WHERE value <> 23.5

Answer 2

Without a date column or an auto-increment column, you can't reliably determine the order the records were entered.

The physical order with which rows are stored in the table is non-deterministic.

Answer 3

If each row has an ID column that is unique and auto incrementing, you could do something like:

SELECT * FROM table WHERE id > (SELECT id FROM table WHERE value = 23.5)

Answer 4

You need to define an order to the results to do this. There is no guaranteed order to the data otherwise. If by "the next 2 rows after" you mean "the next 2 records that were inserted into the table AFTER that particular row", you will need to use an auto incrementing field or a "date create" timestamp field to do this.

Answer 5

You can obviously write a program to do that but i am assuming you want a query. What about using a Union. You would also have to create a new column called value_id or something in those lines which is incremented sequentially (probably use a sequence). The idea is that value_id will be incremented for every insert and using that you can write a where clause to return the remaining two values you want.

For example:

Select * from table where value = 23.5 Union Select * from table where value_id > 2 limit 2;

Limit 2 because you already got the first value in the first query

Answer 6

You need an order if you want to be able to think in terms of "before" and "after".

Assuming you have one you can use ROW_NUMBER() (see more here http://msdn.microsoft.com/en-us/library/ms186734.aspx) and do something like:

With MyTable
(select row_number() over (order by key) as n, key, name, value
from table)

select key, name, value
from MyTable
where n >= (select n from MyTable where value = 23.5)