SQL - Getting "Best Match" Results Only [Answered]
-
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 Locale 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).
-
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 isWITH 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;
-
I've used this trick before too.
row_number
ordered by your preference criteria, which can be ranked piecemeal eg withcase
.
I'd probably useLocale in ('es-MX', 'es', '')
just to be concise.
-
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.
-
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
-
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.
-
@ChaosTheEternal nothing wrong with using a cte to build a ranking "table" with row_number. :-)