CODEWITHSUNDEEP
mysql
Netorica
Netorica

Reputation: 19327

Return records from first record to get the number of records needed to return

Ok I really don't know how to explain this even in the title but this is what I want.

first I do have 5 records

id | name  |
------------
1  | ringo |
------------
2  | nashi |
------------
3  | momo  |
------------
4  | manga |
------------
5  | tokyo |

now I produce a random number on what row I will start querying. For example I got a random number of 4 and so I will get rows 4 and 5. But my problem is I need 4 records on every query. So it means I will go back to the first record and get the first two rows.

Is there any possible way that I can go back on the first row if my query results lacks the number of records I want?

This is a connected question Select from nth record and so on in MySQL that shows what I have done so far.

Upvotes: 0

Views: 77

Answers (2)

eggyal
eggyal

Reputation: 125855

If you sort the table first by records greater than the pivot id, then by id, you need only LIMIT the resultset to the first four records:

SELECT * FROM my_table ORDER BY id >= 4 DESC, id LIMIT 4

See it on sqlfiddle.

Upvotes: 3

inhan
inhan

Reputation: 7470

Here's what I would do.

SELECT * FROM `your_table`
WHERE `id` >= 3 /* 3 is some random number */
UNION
SELECT * FROM `your_table`
WHERE `id` < 5
LIMIT 4

EDIT: eggyal's question in his comment is something worth considering. My suggestion with that option considered is below.

SELECT * FROM `your_table`
WHERE `id` >= 3 /* 3 is some random number */
UNION
SELECT * FROM (
    SELECT * FROM `your_table` LIMIT 4
) a
LIMIT 4

Upvotes: 1

Related Questions