CODEWITHSUNDEEP
phpmysql
Werner
Werner

Reputation: 1847

MySql: Get number of row from query where id matches

I have a complicated select like:

select id from table
  left join...
  left join... (a lot of joins)
where ... (a lot of ANDs and ORs)
order by... (a lot of orders)

and I'm getting a result like:

1234
5565
7212
2212
etc.

I have an id which belongs to the result-set, like 7212, and want to know which row in the result-set matches the id (starting with row 0 this would be row 2 in my example).

Right now I'm reading all data and compare it in php, but I was wondering if there is a SQL-statement which does that for me and results 2 when entering 7212.

In fact I want to get the previous and next ids (row 1 and row 3 = 5565 and 2212), if thats somehow possible in 1 query that would be even better.

Upvotes: 1

Views: 202

Answers (2)

Che-Chia Chang
Che-Chia Chang

Reputation: 291

Add an auto_increment index for each selected rows:

SELECT  
    @i:=@i+1 as index, 
    id 
FROM table, (SELECT @i:= 0) AS i
    LEFT JOIN...
    LEFT JOIN...(a lot of joins)
WHERE ... (a lot of ANDs and ORs)
ORDER BY... (a lot of orders)

Give you this:

index id
1     1234
2     5565
3     7212
4     2212

Upvotes: 1

PhilS
PhilS

Reputation: 365

You can select the number row:

select @rownum:=@rownum+1 No, foo, bar from table, (SELECT @rownum:=0) r;

It's a possible duplicate of the question asked here: How to show sequential number in MySQL query result

Upvotes: 1

Related Questions