CODEWITHSUNDEEP
mysqlsqlmax
paul
paul

Reputation: 69

Find the top N countries with highest GDP during tome period

I have a table with the following columns: country_id, country_name, continent, expenditure, date(yyyymmdd), population. I want to find the n top (3 for example) countries with the highest expenditure for each day during time period (20170101 - 20170107).

SELECT DISTINCT country_name, expenditure 
FROM  expend_per_day
GROUP BY date
HAVING date BETWEEN "20170101" AND "20170107"
LIMIT 3;

But i dont get what i want, it shows me the first 3 rows. I want that for each day to see 3 countries with the most highest expenditure and the expenditure it self. Do you have any ideas? Thank you

Upvotes: 0

Views: 60

Answers (1)

Akina
Akina

Reputation: 42728

WITH cte AS ( SELECT *, 
                     ROW_NUMBER() OVER (PARTITION BY `date` ORDER BY expenditure DESC) rn
              FROM expend_per_day
          --  WHERE `date` BETWEEN '20170101' AND '20170107' 
            )
SELECT *
FROM cte
WHERE rn <= 3

If there exists more than one row with the same expenditure for the same date as for 3rd row then indefinite row from these duplicated will be selected.

Upvotes: 1

Related Questions