Ranking a Two-Dimensional Catalog With One SQL Expression

Every marketplace, catalog or directory app eventually hits the same question: given two independent dimensions, which rows do I show first? For us the dimensions are country and industry, and the rows are 1,300+ local business directories. A plumber in Munich and a law firm in Toronto need overlapping-but-different lists out of the same table.

The naive answers are all bad. Two dimensions of relevance is exactly the shape where "just add a WHERE clause" produces empty results and "just order by popularity" produces a list that ignores the user.

This is the scoring expression we landed on, why each piece is there, and the two bugs that cost us the most time.

The data model

Three tables. One catalog, two mapping tables:

directories(id, name, url, domain_rating, is_global, active, ...)
directory_countries(directory_id, country_code, rank)   -- rank within that country
directory_industries(directory_id, industry_slug, rank) -- rank within that vertical

A row can be mapped to many countries, many industries, both, or neither. is_global marks the handful that are relevant everywhere (Foursquare, OpenStreetMap and friends). Each mapping carries its own rank, curated per dimension: rank 10 in Germany says nothing about rank in Japan.

The query takes (p_country, p_industry) and must return every relevant row, best first, with p_industry optional.

Attempt 1: the inner join that eats your catalog

-- do not do this
select d.* from directories d
join directory_countries dc on dc.directory_id = d.id and dc.country_code = $1
join directory_industries di on di.directory_id = d.id and di.industry_slug = $2;

This returns only rows mapped to both, which in a sparse catalog is close to nothing. Worse, it silently drops the global anchors that every user needs, because a genuinely worldwide directory is often mapped to no single country at all.

Sparse many-to-many data punishes inner joins. The mappings are the evidence, not the filter.

Attempt 2: LEFT JOIN, and let the score decide

select d.id, d.name, d.domain_rating, dc.rank as country_rank, di.rank as industry_rank
from directories d
left join directory_countries  dc on dc.directory_id = d.id and dc.country_code  = $1
left join directory_industries di on di.directory_id = d.id and di.industry_slug = $2
where d.active
  and (d.is_global or dc.country_code is not null or di.industry_slug is not null);

Two things matter here.

The join predicate does the filtering, not the WHERE. The and dc.country_code = $1 sits in the ON clause, so non-matching rows survive with NULL mapping columns. Move that condition to WHERE and you have silently rewritten your LEFT JOIN into an INNER JOIN. It is the most common bug in this shape of query, and it fails quietly: you get a shorter list, not an error.

The WHERE clause is the relevance floor. A row qualifies if it is global, or mapped to this country, or mapped to this industry. Everything else is noise for this user, however strong its authority score.

The scoring expression

Now ordering. What we want, in plain language:

1. Directories curated for this country come first, because local search is local.
2. Global anchors sit alongside them when no industry is selected.
3. Industry-only matches come next.
4. Everything else trails.
5. Within a tier, use the curated rank; break ties by authority.

That is a tier plus an offset, which is one arithmetic expression:

(
  case
    when dc.country_code is not null then 0     -- mapped to this country
    when d.is_global and di.industry_slug is null then 0
    when di.industry_slug is not null then 1000 -- industry-only match
    else 2000                                   -- global, but an industry was asked for
  end
  + least(
      coalesce(di.rank, 999),
      coalesce(dc.rank, 999),
      case when d.is_global then 60 else 999 end
    )
)::int as relevance

The tier constants are spaced by 1000 while the offset is bounded by 999, so a tier can never leak into the one below it. That is the whole trick: reserve a numeric range per tier wider than any possible in-tier score. No window functions, no subqueries, no application-side merging.

least() over the three ranks means a row is placed by its strongest claim to relevance. A directory ranked 8 in Germany and 400 in "restaurants" is treated as an 8, because for this user its German relevance is the reason it appears at all.

The case when d.is_global then 60 is a synthetic rank: global anchors have no curated per-country rank, but they are not unranked either. Sixty places them below a country's top-tier native directories and above its long tail, which is exactly where they belong.

The COALESCE sentinel, and its trap

coalesce(di.rank, 999) looks harmless. It is, right up until someone curates a rank of 1000 and it sorts above an unranked row. Sentinels are a contract: pick a value outside the legal range and enforce that range at write time, or use 999999 and stop worrying. We kept 999 and added a check constraint. Do one or the other; do not leave it implicit.

Ordering, including the boolean

order by relevance asc,
         d.domain_rating desc nulls last,
         (d.recipe_status in ('verified','draft')) desc,
         d.name;

nulls last is not optional. In Postgres, NULL sorts first in DESC order, so without it every directory whose authority score we do not have would outrank every directory we scored. Our catalog holds a rating for a minority of rows, so this one clause was the difference between a credible list and a nonsense list.

The third key sorts a boolean: rows we can actually submit to automatically win the tie against rows a human would have to do by hand. Postgres orders false < true, so desc puts true first.

Two bugs worth stealing

Bug one: the disappearing catalog. Moving a join predicate from ON to WHERE turned the LEFT JOINs into inner joins. Every market with a thin native layer went from roughly thirty rows to a handful, and because a shorter list still looks like a list, it survived a review. The test that catches it is not "does the query return rows" but "does it return the global anchors for a country with zero native mappings".

Bug two: NULL-first ordering. Described above. Also silent, also plausible-looking. Both bugs share a signature: wrong output that is well-formed. Write the assertion against a known-sparse input, not the happy path.

Does the ranking hold up?

We measured the resulting lists across the whole catalog, and the honest finding was that most verticals are much thinner than the industry's "500 citations!" marketing suggests: per-category directory counts run from about 95 down to about 32, and ten of 45 categories have no category-specific platform at all. The wider 2026 data study covers method and distribution.

That matters for ranking design: when the true relevant set is a few dozen rows, precision at the top of the list is everything, and a fancy ML re-ranker is not the missing piece. A curated rank plus a tier offset gets you most of the way.

When not to build this

If you need the ranked list for one specific business rather than a general catalog, the build-versus-buy line is short: the query is a weekend, the curation behind those rank columns is the years. That is the actual product. We wrote up how citation building works end to end for the non-SQL version of the same problem, and there is a comparison of the commercial tools if you would rather not own the data at all.

Takeaways

• Two-dimensional relevance wants LEFT JOINs plus a score, not a compound WHERE.
• Encode tiers as spaced integer bands wider than any in-tier offset.
• least(coalesce(...)) places a row by its strongest claim.
• nulls last on any partially-populated DESC key, always.
• Test against sparse input, because this whole class of bug returns a plausible wrong answer instead of an error.