CODEWITHSUNDEEP
mysqlselectgreatest-n-per-group
bhargav patel
bhargav patel

Reputation: 1

Mysql Select query with limit

I have MySQL table for US zip codes like 

id     | zip_code  | city       |  State
1      |   99553   | Akutan     |  Alaska
2      |   99571   | Cold Bay   |  Alaska
3      |   99583   | False Pass |  Alaska 
4      |   36006   | Billingsley|  Alabama 
5      |   36008   | Booth      |  Alabama
6      |   36051   | Marbury    |  Alabama

.....

I want to use single select MySQL query to fetch only 2 cities for every states so final result look like below table

id     | zip_code  | city       |  State
1      |   99553   | Akutan     |  Alaska
2      |   99571   | Cold Bay   |  Alaska
4      |   36006   | Billingsley|  Alabama 
5      |   36008   | Booth      |  Alabama

Upvotes: 0

Views: 41

Answers (1)

GMB
GMB

Reputation: 222382

If you are running MySQL 8.0, you can use row_number():

select id, zip_code, city, state
from (
    select t.*, row_number() over(partition by state order by zip_code) rn
    from mytable t
) t
where rn <= 2

In earlier versions, you could use a correlated subquery for filtering:

select t.*
from mytable t
where (
    select count(*) 
    from mytable t1 
    where t1.state = t.state and t1.zip_code <= t.zip_code
) <= 2

Upvotes: 1

Related Questions