I want to make a query that searches the table for names of cities and that the result will be names of cities that start with the letter b and then names of cities that contain the letter b.

select *
from tb_city
where name like 'b%' or name like '%b%'

How can I first get the cities that start with the letter b and then the cities that contain the letter b? The result I get is mixed

CodePudding user response：

you need to use union all for this.

select *
from tb_city
where name like 'b%' 
union all
select *
from tb_city
where  name like '%b%' and name not like 'b%'

CodePudding user response：

For conditional logic where you want to inspect and sort by the results of individual constraints you can use UNION, but you will need to add a discriminator or sort indicator column to identify the difference between the result sets. Note that this will however result in duplicate rows unless you deliberately exclude the 'b%' from the '%b%' result sets:

SELECT *, SortOrder = 0
FROM tb_city
WHERE name LIKE 'b%' 
UNION ALL
SELECT *, SortOrder = 1
FROM tb_city
WHERE name like '%b%' and name NOT LIKE 'b%'
ORDER BY SortOrder

NOTE: only the last SELECT in the set can have the ORDER BY clause, the order is evaluated after the result sets are combined

An alternate solution that works well form multiple criteria, is to use a case statement to determine the sort. Using CASE will make it easier to ensure that you only get 1 of each record, without having to write the criteria as a mutually exclusive set:

SELECT *
FROM tb_city
WHERE name LIKE 'b%' or name LIKE '%b%'
ORDER BY CASE 
             WHEN name LIKE 'b%' THEN 0 
             WHEN name LIKE '%b%' THEN 1
             ELSE 2
         END

NOTE: It is good practise to specify a default constraint in your case statements, especially when using this conditional expression for sorting. If your WHERE criteria becomes out of sync with the CASE it might result in additional rows in the results, if no ELSE is specified then the extra rows will have a value of NULL, in SQL Server that will mean they will be sorted before the 0s. (In other RDBMS this behaviour is configurable and the defaults may not be what you expect...)

• Remove any ambiguity be being explicit with your sorting and make sure you cover all bases.

The problem with a resultset like this that uses CASE directly in the ORDER BY though can be that it is hard to visualise why the results are rendered in that order, it can be hard to confirm the sequence logic.

If you want to debug the sorting criteria or just need to know why the data is sorted in a specific way, you can moe the CASE out to an expression based column in the set, like the UNION solution, we add in the discriminator column:

SELECT *
     , CASE 
           WHEN name LIKE 'b%' THEN 0 
           WHEN name LIKE '%b%' THEN 1
           ELSE 2
       END as SortOrder
FROM tb_city
WHERE name LIKE 'b%' or name LIKE '%b%'
ORDER BY SortOrder

There is no significant performance difference between this and the previous query other than the additional bytes over the wire, by referencing the alias of the SortOrder expression the expression will not be re-evaluated.