Geek asks and answers

Rank not properly defined

I am using this query:

SELECT A.place_idx,A.place_id,B.TOTAL_CNT,(@r := @r + 1) AS rank FROM CUSTOM_LIST
AS A
INNER JOIN
(SELECT  @r := 0)
AS C
INNER JOIN
(SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
AS B ON B.place_id=A.place_id order by B.TOTAL_CNT desc;

What gives this result:

1

But I want this result:

2

How do I change my request? What am I doing wrong?

+5
source share
3 answers
SELECT *,(@r := @r + 1) AS rank FROM 
(
  SELECT A.place_idx,A.place_id,B.TOTAL_CNT FROM CUSTOM_LIST
  AS A
  INNER JOIN
  (SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
  AS B ON B.place_id=A.place_id order by B.TOTAL_CNT desc
) AS T, (SELECT  @r := 0) AS tt
+5
source

You want to add

ORDER BY rank DESC

into your SQL query to display the rank numbers. You can also change DESC to ASC to reorder.

EDIT

I see that you already have an order, so just edit it like this.

You must add it with a comma before this:

ORDER BY column1 DESC , column2 DESC
0
source

C.rank , . , . , , .. , .

Alternatively, you can put what you have in your internal selection and then do the ranking after.

0
source

More articles:


All Articles