mysql show position in resultset

Question

So I have a list of say 10 rows. Each with an key and value.

Is there a way in mysql where I can, get a rows position in a given resultset:

SELECT 'resultSetPosition' FROM table WHERE rowKey=x ORDER BY rowValue

id        value
 1        a
 2        b
 3        c

 o/p required

 if  id 3 the i need to get position of that row is 3

EDIT: I only want to get one row out in my resultset, but the 'position' should relate to its position for example if you sort by id.

(Obviously this whole thing is easy if I just pull the entire resultset and search the array in my programming, but I wanted to see if it could be done in mysql alone.)

possible duplicate of [With MySQL, how can I generate a column containing the record index in a table?](http://stackoverflow.com/questions/3126972/with-mysql-how-can-i-generate-a-column-containing-the-record-index-in-a-table) — Buggabill, Jan 13 '15 at 13:42
Try looking at this answer that looks similar to your problem: http://stackoverflow.com/a/4474389/1418400 — sfj, Jan 13 '15 at 13:48

score 1 · Accepted Answer · answered Jan 13 '15 at 13:51

1

You can try this:-

SELECT *, @rownum := @rownum + 1 AS ROW_NUMBER
FROM TABLE
JOIN (SELECT @rownum := 0) R;

This might help you.

answered Jan 13 '15 at 13:51

Ankit Bajpai

13,128
4
25
40

score 1 · Answer 2 · answered Jan 13 '15 at 14:14

1

Try this:

SET @rank=0;
SELECT @rank:=@rank+1 AS resultSetPosition
 FROM tableName
 WHERE rowKey=x
 ORDER BY rowValue

Also take a look at this link.

answered Jan 13 '15 at 14:14

Ataboy Josef

2,087
3
22
27

score 0 · Answer 3 · answered Jan 13 '15 at 13:51

0

Try with this hope this will clear your problem

 select count(*) as pos from table_name where id<=current_id

answered Jan 13 '15 at 13:51

Vamshi .goli

522
4
13

mysql show position in resultset

3 Answers3