9

I'm a MS-SQL Developer, now I use this query (MySQL) ↓

SELECT A.place_idx,A.place_id,B.TODAY_CNT,C.TOTAL_CNT FROM CUSTOM_LIST 

AS A

INNER JOIN
(SELECT place_id,COUNT(place_id) AS TODAY_CNT from COUNT_TABLE where DATE(place_date) = DATE(NOW()) GROUP BY place_id)
AS B ON B.place_id=A.place_id

INNER JOIN
(SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
AS C ON C.place_id=A.place_id

The result is:

enter image description here

I want this:

enter image description here

nawfal
  • 70,104
  • 56
  • 326
  • 368
KOREAN_DEVELOPER
  • 141
  • 4
  • 1
    Where is Rank? I mean in which table? – talha2k Sep 13 '12 at 11:25
  • 2
    MySQL doesn't support analytic functions. You can achieve what you want with [user variables](http://dev.mysql.com/doc/en/user-variables.html), but it would be easier (if at all possible) to simply `ORDER BY TOTAL_CNT DESC` and add the ranking from within your application code. – eggyal Sep 13 '12 at 11:27
  • @eggyal adding the rank i easy, once you know how. I had the same problem a while ago. See below. – PiTheNumber Sep 13 '12 at 11:29
  • 1
    Did you search for this? http://stackoverflow.com/questions/3490682/how-to-get-the-position-of-sorted-rows-using-mysql-and-php – Ron van der Heijden Sep 13 '12 at 11:30
  • none of the current answer returns what's shown in the image! What do you want? Sort the table? Add a rank column and keep the order, or add rank and sort? – PiTheNumber Sep 13 '12 at 11:32
  • sorry.. i'm korean developer i won't add rank and sort.. – KOREAN_DEVELOPER Sep 13 '12 at 11:44
  • no need to be sorry. It does no matter where you are from or how good your English is as long we figure out what you want ;) Is your question solved with the query below? – PiTheNumber Sep 13 '12 at 11:55
  • @user1668402 Have a look here sqlfiddle.com/#!2/8a2e6/1 – RL89 Sep 13 '12 at 13:02
  • Check this answer, too: [Sorting the Table and getting the position](http://dba.stackexchange.com/questions/18316/sorting-the-table-and-getting-the-position/18326#18326). – ypercubeᵀᴹ Sep 13 '12 at 13:45

2 Answers2

4

Try somethink like this:

SELECT ..., C.TOTAL_CNT, (@r := @r + 1) AS rank FROM CUSTOM_LIST, (SELECT  @r := 0) t
...
ORDER BY C.TOTAL_CNT DESC

Whole query:

SELECT A.place_idx,A.place_id,B.TODAY_CNT,C.TOTAL_CNT, (@r := @r + 1) AS rank 
FROM CUSTOM_LIST AS A, (SELECT  @r := 0) t

INNER JOIN
(SELECT place_id,COUNT(place_id) AS TODAY_CNT from COUNT_TABLE where DATE(place_date) = DATE(NOW()) GROUP BY place_id)
AS B ON B.place_id=A.place_id

INNER JOIN
(SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
AS C ON C.place_id=A.place_id

ORDER BY C.TOTAL_CNT DESC

What if we got two same values in Total_CNT?

Maybe something like this:

SELECT ..., (@last := C.TOTAL_CNT) AS TOTAL_CNT, 
  IF(@last = C.TOTAL_CNT, @r, @r := @r + 1) AS rank
FROM CUSTOM_LIST, (SELECT  @r := 0, @last := -1) t
...
PiTheNumber
  • 22,828
  • 17
  • 107
  • 180
-1

Updated

RANK() OVER (ORDER BY TOTAL_CNT DESC DESC) AS Rank

Here I got Another Very Good Solution.:

SELECT A.place_idx,A.place_id,B.TODAY_CNT,C.TOTAL_CNT, RANK() OVER (ORDER BY TOTAL_CNT DESC) AS Rank FROM CUSTOM_LIST 

AS A

INNER JOIN
(SELECT place_id,COUNT(place_id) AS TODAY_CNT from COUNT_TABLE where DATE(place_date) = DATE(NOW()) GROUP BY place_id)
AS B ON B.place_id=A.place_id

INNER JOIN
(SELECT place_id,COUNT(place_id) AS TOTAL_CNT from COUNT_TABLE GROUP BY place_id)
AS C ON C.place_id=A.place_id
PiTheNumber
  • 22,828
  • 17
  • 107
  • 180
RL89
  • 1,866
  • 5
  • 22
  • 39
  • 2
    if think `RANK() OVER` is not supported in mysql. Isn't it oracle? http://stackoverflow.com/questions/1438764/rank-over-in-mysql – PiTheNumber Sep 13 '12 at 12:53