Get row_number of a specificatet column MySQL

Asked Sep 26 '17 at 18:06

Active Sep 26 '17 at 18:12

Viewed 19 times

Database: MySQL columns: city, create2 (Timestamp)

I have this query:

SELECT city ,COUNT(city) 
FROM content 
WHERE create2 > DATE_SUB( NOW(), INTERVAL 7 DAY)   '
GROUP BY city 
ORDER BY COUNT(city) DESC

Now i want to get the ranked position from a city, but i didn't found a solution. Like this:

SELECT city ,COUNT(city)    
FROM content 
WHERE create2 > DATE_SUB( NOW(), INTERVAL 7 DAY)   
GROUP BY city 
ORDER BY COUNT(city) DESC 
WHERE city= Berlin

edited Sep 26 '17 at 18:12

Barmar

741,623
53
500
612

asked Sep 26 '17 at 18:06

Next World Soft

In php get it by from index of returned array – M Khalid Junaid Sep 26 '17 at 18:11
Where are you calculating ranks? MySQL doesn't have `ROW_NUMBER`. – Barmar Sep 26 '17 at 18:13
Put the query that calculates the rank in a subquery, then select the city you want in the main query. – Barmar Sep 26 '17 at 18:13
This is a notorious pain in the neck in MySQL. Look at the highest-voted answer in the duplicate question. – O. Jones Sep 26 '17 at 18:15
with the SubQuery I do not get that, I've already tried all day. – Next World Soft Sep 26 '17 at 18:15
Which query would you advise me to get to my goal? – Next World Soft Sep 26 '17 at 18:16

Get row_number of a specificatet column MySQL

0 Answers0