How to select some rows from MySQL table using theirs numbers?

Question

I have a set of rows (it is actually filtered and ordered mysql table).
I also have a set of integer numbers like (5,9,12,67,2,3) which I want to use as row numbers.

Question: What is better way to fetch rows from this set using these numbers?

Example: I have a set 15 rows. And numbers (1,5,7). How I can get 1st,5th and 7th rows?

maybe [this](http://stackoverflow.com/questions/2728413/equivalent-of-oracles-rowid-in-mysql) will give you hint.. but why you are playing with rownumber? also [read this](http://stackoverflow.com/a/11130318/1066828) — Fahim Parkar, Jul 28 '13 at 21:03
@FahimParkar I have a crazy selection algorithm, which should get N random lines from given set of rows. Line numbers generated by special complex algorithm, so I can not use MySQL for it. — Alexey Khaydukov, Jul 28 '13 at 21:06
How do you get the set of rows? Is it just a SQL query or are you using some other programming language as well? — Code-Apprentice, Jul 28 '13 at 21:16
I've tried this code and it looks like works as I am expected. select * from (SELECT *, @rowid:=@rowid+1 as rowid FROM `games`, (SELECT @rowid:=0) as init WHERE gametype_id = 10 order by rating desc) t2 where t2.rowid in (1,3,19,27,57,59,161,171,276,686,867,1091,1896,1997,2536,2740) — Alexey Khaydukov, Jul 28 '13 at 21:30
@AlexeyKhaydukov Welcome to Stack Overflow. FYI, you can expedite answers to your question by taking the important parts from your actual code and creating a *complete* example that illustrates what you are asking about. Post the example here along with your question. Just be sure that when someone else runs your example, it recreates the *exact* results that you are asking about and does not introduce any irrelevant errors or side effects that can confuse the issue. — Code-Apprentice, Jul 28 '13 at 21:56

Repeat Spacer · Answer 1 · 2013-07-28T21:48:10.130

0

Sorry I just singed up and don't know the good habit to answer so I'll try this:

If You have auto_increment index use that?

If not you can make arbitrary number like

SELECT @currentRow := @currentRow + 1 AS row_number, *
FROM table, (SELECT @currentRow:=0) as init 
WHERE row_number IN 1,5,7

(something is wrong with WHERE)

Yes i got it figured out but @Epsiloncool seemd to do it much faster.

Triple select helps you to get where:

SELECT * FROM
(SELECT *, @currentRow := @currentRow + 1 AS row_number
  FROM table t, (SELECT @currentRow := 0) AS init) AS table
WHERE cdees.row_number IN 1,5,7

edited Jul 28 '13 at 21:48

answered Jul 28 '13 at 21:15

Repeat Spacer

373
3
17

Actually I tested this and it's not working :D and there is also typo "WHERE row_number". – Repeat Spacer Jul 28 '13 at 21:21
from @Alexey i realized i was missing the init of currentRow. But still the WHERE is somehow not working. – Repeat Spacer Jul 28 '13 at 21:36
yes, I've tried it early, but it will not work, because row_number is counted value which is not available in the where body, but not real column. – Alexey Khaydukov Jul 28 '13 at 21:38

score 0 · Accepted Answer · answered Jul 28 '13 at 21:35

0

Try to use this code select * from (SELECT *, @rowid:=@rowid+1 as rowid FROM table, (SELECT @rowid:=0) as init WHERE ... order by ...) t where t.rowid in (...set of numbers...)

answered Jul 28 '13 at 21:35

Epsiloncool

1,435
16
39

How to select some rows from MySQL table using theirs numbers?

2 Answers2