CODEWITHSUNDEEP
phpmysql
Undermine2k
Undermine2k

Reputation: 1491

mysql select row number query

I'm having trouble with this query im trying to select the row number based on (upvotes - downvotes) in a certain subset of a table , but i need to also reset the @rownum within the same query:

This query works but I need to run two of them. Anyway to combine the set @rownum into one query my attempts have been in vain.

SET @rownum = 0;
SELECT rank  
FROM (SELECT *, @rownum:=@rownum + 1 AS 
rank FROM Cars where Auto_SET = 'BMW'
order by (upvotes-downvotes) DESC) d
WHERE ID = 391802

This one throws an error:

SELECT rank 
FROM (SELECT *, @rownum:=@rownum + 1 AS 
rank FROM Cars  where Auto_SET = 'BMW' , 
(SELECT @rownum:=0) r order by 
(upvotes-downvotes) DESC) d
WHERE ID = 391913

Upvotes: 0

Views: 86

Answers (2)

angelcool.net
angelcool.net

Reputation: 2546

Something like this should also work:

SELECT @rn := @rn+1 AS RANK,t1.* FROM (

 # Your query here.
 SELECT * FROM Cars WHERE Auto_SET = 'BMW' 
 WHERE ID = 391913 ORDER BY (upvotes-downvotes) DESC

) t1, (SELECT @rn:=0) t2;

Good luck!!

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

The cross-join should be along with the select from table_name something like

SELECT rank 
FROM (
  SELECT *, 
  @rownum:=@rownum + 1 AS rank 
  FROM Cars ,(SELECT @rownum:=0) r 
  where Auto_SET = 'BMW' 
  order by 
 (upvotes-downvotes) DESC
) d
WHERE ID = 391913

Upvotes: 1

Related Questions