mysql select row number query

Question

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

Throws an error. What error and where?

David Soussan
– David Soussan

2015-03-18 07:05:54 +00:00
Commented Mar 18, 2015 at 7:05 — David Soussan
– David Soussan, Commented Mar 18, 2015 at 7:05

Abhik Chakraborty · Accepted Answer · 2015-03-18 07:15:59Z

1

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

answered Mar 18, 2015 at 7:15

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

angelcool.net · Accepted Answer · 2015-07-22 04:02:58Z

0

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!!

answered Jul 22, 2015 at 4:02

angelcool.net

2,5761 gold badge26 silver badges26 bronze badges

Collectives™ on Stack Overflow

mysql select row number query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related