Common mistake

SQL: Why rank() = 1 Returns More Than One Row

Tied rows share rank 1, so a top-one-per-group query can return two winners. Break the tie on purpose, or decide that ties should all win.

The symptom

You want the single best customer in each state, so you rank customers by order count within each state and keep rank = 1. Garden Shop customers live in 7 states, and the query returns 10 rows. California, Ohio, and Texas each have two "top" customers.

Why it happens

rank() gives tied rows the same number. Mia and Sofia both placed 2 orders, the most of any California customer, so both are rank 1. That's what rank() is for: a race with a tie for first has two winners. dense_rank() does the same at the top. The query did what it says; it just doesn't answer "one per state".

The trap is that it only shows up when the data has a tie at the top. Run the same query next month and it may return one row per state, so the bug ships and a dashboard later double-counts a state.

The fix

Decide what should break the tie, and use row_number(), which never gives two rows the same number. Here the longest-standing customer wins (earliest signup_date), with customer_id as a last resort so the answer can't change between runs.

Wrong: rank() = 1 keeps ties

with ranked as (
  select c.state,
         c.customer_id,
         c.first_name,
         count(*) as order_count,
         rank() over (
           partition by c.state
           order by count(*) desc
         ) as state_rank
  from customers as c
  join orders as o on o.customer_id = c.customer_id
  group by c.state, c.customer_id, c.first_name
)
select state, customer_id, first_name, order_count
from ranked
where state_rank = 1
order by state, customer_id

Right: row_number() with a tie-breaker

with ranked as (
  select c.state,
         c.customer_id,
         c.first_name,
         count(*) as order_count,
         row_number() over (
           partition by c.state
           order by count(*) desc, c.signup_date, c.customer_id
         ) as state_rank
  from customers as c
  join orders as o on o.customer_id = c.customer_id
  group by c.state, c.customer_id, c.first_name, c.signup_date
)
select state, customer_id, first_name, order_count
from ranked
where state_rank = 1
order by state, customer_id

Seven rows, one per state. Mia signed up before Sofia, so she wins California. The tie-breaker is a business decision, not a SQL one: "most recent order" or "highest lifetime revenue" would be just as valid, and might pick someone else.

Sometimes ties should all win

If the question is "which customers placed the most orders in each state", both of California's customers are a correct answer, and rank() = 1 is the right query. The mistake is using it when the report, a join, or a chart expects exactly one row per group.