MSSQL select query with prioritized OR-CodePudding

I need to build one MSSQL query that selects one row that is the best match.

Ideally, we have a match on street, zip code and house number.
Only if that does not deliver any results, a match on just street and zip code is sufficient

I have this query so far:

SELECT TOP 1 * FROM realestates
WHERE 
    (Address_Street = '[Street]'
AND Address_ZipCode = '1200'
AND Address_Number = '160')
OR  
(Address_Street = '[Street]'
AND Address_ZipCode = '1200')

MSSQL currently gives me the result where the Address_Number is NOT 160, so it seems like the 2nd clause (where only street and zipcode have to match) is taking precedence over the 1st. If I switch around the two OR clauses, same result :)

How could I prioritize the first OR clause, so that MSSQL stops looking for other results if we found a match where the three fields are present?

CodePudding user response：

The problem here isn't the WHERE (though it is a "problem"), it's the lack of an ORDER BY. You have a TOP (1), but you have nothing that tells the data engine which row is the "top" row, so an arbitrary row is returned. You need to provide logic, in the ORDER BY to tell the data engine which is the "first" row. With the rudimentary logic you have in your question, this would like be:

SELECT TOP (1)
       {Explicit Column List}
realestates
WHERE Address_Street = '[Street]'
  AND Address_ZipCode = '1200'
ORDER BY CASE Address_Number WHEN '160' THEN 1 ELSE 2 END;

CodePudding user response：

You can't prioritize anything in the WHERE clause. It always results in ALL the matching rows. What you can do is use TOP or FETCH to limit how many results you will see.

However, in order for this to be effective, you MUST have an ORDER BY clause. SQL tables are unordered sets by definition. This means without an ORDER BY clause the database is free to return rows in any order it finds convenient. Mostly this will be the order of the primary key, but there are plenty of things that can change this.