Re: SQL SELECT: controlling the order of evaluation

[Peter Dow <petercdow@xxxxxxxxx>]

You want to only return 1 record, with priority given to a match on zip, 
then city, etc.?

Maybe something like

select {}
     , case when zip=33166 then 1
            when city = 'MIAMI' then 2
            when county = 'DADE' then 3
            when state = 'FL' then 4
            else 9
        end Priority
   from {}
 where state='FL'
    or county='DADE'
    or city='MIAMI'
    or postal='33166'
 order by priority
 fetch first row only


On 2/2/2023 12:25 PM, x y wrote:
> When I have multiple conditions on my WHERE clause, can I control the order
> in which they're evaluated?
>
> Example: the client in postal code 33166, Miami, Dade County, Florida, is
> looking for a local resource.  I'd like to write
> select {} from {} where state='FL' or county='DADE' or city='MIAMI' or
> postal='33166' fetch first row only
> and then tell SQL to return a result by evaluating first on postal, then
> city, then county, and then state.
>
> I'm trying to avoid multiple SELECT statements.  It's impractical to add
> "helper" data in the table, which will have > 100 million records.  Putting
> "postal='33166'" first doesn't provide the desired result.  A UNION ALL
> won't work.  I don't think a JOIN will work but I'm trying to figure out
> how it might work.
>
> It looks like SQL selects the first row that meets any of the selection
> criteria, i.e., SQL is giving all the conditions equal weight).  I'm using
> CASE in my ORDER BY for other requirements but I can't figure out if that's
> the solution here.
>
> Thanks for your ideas!
>
> --x