SQL - Getting "Best Match" Results Only [Answered]

ChaosTheEternal

To be simple, I'm restricted to SQL Server 2008 compatibility and here is the sample table I have:

ID	FKID	Locale	Text
1	1		Item 1 English
2	1	es	Item 1 Spanish
3	1	fr	Item 1 French
4	2		Item 2 English
5	2	es	Item 2 Spanish
6	3		Item 3 English

My goal is to have a single query where I will search for a proper locale string (e.g. "en-US", "es-MX") and gives back a list of only the "best matches" for each FKID by falling back as necessary, so:

Querying for "es-MX" (or any "es-XX" locale) gives

ID	FKID	Locale	Text
2	1	es	Item 1 Spanish
5	2	es	Item 2 Spanish
6	3		Item 3 English

While querying for "en-US" (or any unlisted locale) gives

ID	FKID	Text
1	1	Item 1 English
4	2	Item 2 English
6	3	Item 3 English

Anyone know how I would write such a query, or if it's even possible?

The reason for the locale fallback implementation is to match .NET resource fallback which works similarly, and the reason for wanting the query to get the results like that is to then join it against the primary table to present the results with localized text (if possible).

Watson

Can you mangle the locale string a bit before it goes in the query? Specifically, split it to Locale='es-MX' OR Locale='es' OR Locale=''? Because my first thought is

WITH rated AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY FKID ORDER BY LEN(Locale) DESC) AS best
FROM translations
WHERE Locale='es-MX' OR Locale='es' OR Locale='')
SELECT ID,FKID,Locale,Text FROM rated WHERE best=1;

error

I've used this trick before too. row_number ordered by your preference criteria, which can be ranked piecemeal eg with case.
I'd probably use Locale in ('es-MX', 'es', '') just to be concise.

boomzilla

I've done similar sorts of things using a case statement to build a proxy column for sorting. So, something like this:

case
  when Locale = :localeParam then 0
  when :localeParam like Locale || '%' then 1
  when Locale is null then 2
  else null
end

That assumes you're passing the full locale names, not just the language part, but I'm sure you could adjust it to whatever you're actually doing, if it's something different.

Gribnit

I'd do this as UNION ALLs with match quality as the (synthetic) first (and first ordinal) column. That lets you prefer the exact match if you get it, explicitly, and takes direct advantage of any available parallelism

ChaosTheEternal

So, I figured I would need to do it with a CTE but ROW_NUMBER never came to mind (likely because I've yet to really use it until this).

But I now have something that works, so thanks all.

Captain

@ChaosTheEternal nothing wrong with using a cte to build a ranking "table" with row_number. :-)