CODEWITHSUNDEEP
mysql
Gleiemeister 2000
Gleiemeister 2000

Reputation: 729

mysql show position in resultset

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.)

Upvotes: 0

Views: 688

Answers (3)

Ataboy Josef
Ataboy Josef

Reputation: 2101

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.

Upvotes: 1

Vamshi .goli
Vamshi .goli

Reputation: 520

Try with this hope this will clear your problem

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

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13519

You can try this:-

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

This might help you.

Upvotes: 1

Related Questions