CODEWITHSUNDEEP
sqlmysqlpartition-by
gollapudi sravani
gollapudi sravani

Reputation: 59

Partition by in mysql

I have a table "countries" which contains population data of countries. There are 5 distinct "region_id"s - 10,20,30,40,50 I have this query:

select name, region_id,
sum(population) over (partition by region_id) as tot_pop
from countries

When I run the query, the rows are returned in the ascending order of "region_id" - in the order 10, 20, 30, 40, 50. Is this beacuse I said "partition by region_id"? So does partition by column_name sort the column_name or am I getting the result this way because the rows themselves were inserted in ascending order of region_id into the table?

Say I have this table:

Pop Region_id
1000 10
2000 20
9000 10
3000 10
2000 20
4000 20

So when partition by region_id is executed I am assuming that the records are taken in the following order:

Pop Region_id
1000 10
9000 10
3000 10
2000 20
2000 20
4000 20

Am I right in assuming so?

Upvotes: 0

Views: 67

Answers (1)

Md Sakil Islam
Md Sakil Islam

Reputation: 46

This query calculates the total population for each region_id and returns the results. The PARTITION BY region_id groups the rows by region_id for the purpose of the SUM(population) calculation, but it does not sort the rows.

To explicitly control the order of the results, you should use the ORDER BY clause in your query.

SELECT name, region_id,
       SUM(population) OVER (PARTITION BY region_id) AS tot_pop
FROM countries
ORDER BY region_id;

When you add ORDER BY region_id, it explicitly sorts the result set by region_id.

Upvotes: 1

Related Questions