Tuple matching is neat. How to simulate one if isn't available

Depending on the requirement, tuple-style query is the best query style everyone should write.

So if your database facilitates tuple matching, write your query in that form instead. To wit, list the countries with the highest population on each region, this query works even there's multiple countries with same population on each region:


First step, find the highest population:

select region, max(population)
from bbc
group by region

Final step, use tuple in your WHERE clause:

select region, name, population 
from bbc 
where (region, population) in
      (select region, max(population)
       from bbc
       group by region)
order by region, name

Great, isn't it? Your query investment on the first step can be integrated seamlessly to another query. Everything works out of the box


Contrast that with database that doesn't facilitate tuple test:

select region, name, population 
from bbc z
where exists
      (select null -- neutral. doesn't invoke Cargo Cult Programming ;-)
       from bbc
       where region = z.region 
       group by region
       having z.population = max(population) )
order by region, name

It's not easy to deduce the intent of the query, sometimes it takes another look to deduce the intent of that query.

There's a way to simulate tuple on your query, put it in join condition instead of in where condition. Remember the first query on this post? It will not go to waste, we will use that and make it seamlessly integrate to another query.

select z.region, z.name, z.population 
from bbc z
join (select region, max(population) as maxpop 
      from bbc 
      group by region) x
      on z.region = x.region and z.population = x.maxpop
order by z.region, z.name

Simulate the query here: http://sqlzoo.net/0.htm