CODEWITHSUNDEEP
sqlsql-server
Vũ Minh Vương
Vũ Minh Vương

Reputation: 163

SQL Server search and orderby

I want to search in SQL Server. When I enter "A bang", and use "Like '%A bang%'", and order by ...

I want to arrange to results with words starting with "A bang" , after that the results contain "A bang" but not start word.

SELECT *
FROM 
    (SELECT 
         ROW_NUMBER() OVER (ORDER BY CLNTPFX ASC, CLNTCOY ASC,
                                     SURNAME ASC, GIVNAME ASC,
                                     CLNTNUM ASC, UNIQUE_NUMBER DESC) ROWN,
         TEMP1.*
     FROM 
         (SELECT *
          FROM CLNTPF
          WHERE CLNTPFX = 'CN'
            AND CLNTCOY = '9'
            AND SURNAME LIKE 'A Bang'
            AND VALIDFLAG = 1) TEMP1) TEMP2
WHERE 
    ROWN > 12
    AND ROWN <= 24

Example expected when search "O"

Owner                         
Owner                         
Owner                         
Owner                         
Owner                         
A Owner
A Owner
A Owner

May you help me?

Thanks all!

Upvotes: 0

Views: 56

Answers (1)

zarruq
zarruq

Reputation: 2465

Assuming that c1 is the column on which you want to sort your data in a way that the words starting with O appear first, then followed by words having O somewhere in text, then followed by all other words, You can add an order by to your query as below.

ORDER BY CASE 
        WHEN c1 LIKE 'O%'
            THEN 1
        WHEN c1 LIKE '%O%'
            THEN 2
        ELSE 3 end;

On your sample data, below is the result.

C1
------------------------
Owner
Owner
Owner
Owner
A Owner 
A Owner 
A Owner

You can check the demo here

Upvotes: 1

Related Questions