Search query, 'order by' priority

search-engine, sql-server

Solution

One way of doing it would be using the CASE keyword:

SELECT name, description, content,
    priority = CASE
        WHEN name LIKE '%' + @query + '%' THEN 1
        WHEN desription LIKE '%' + @query + '%'  THEN 2
        WHEN content LIKE '%' + @query + '%'  THEN 3
    END CASE
FROM tbl_content
WHERE
    name LIKE '%' + @query + '%' OR
    desription LIKE '%' + @query + '%'  OR
    content LIKE '%' + @query + '%'
ORDER BY priority ASC

Problem

I need implement simple search in small content table: id, name, description, content. Results must be order by priorities - name - description - content it's mean that if searched word was found in description field, it will be shown only after all rows that have dog in name field What I did: I tried create temp table with structure like table that I use but with another field priority. for each field, these I use for search, do insert select to temp table Example: ``` DECLARE @query NVARCHAR(255) CREATE TABLE #tbl_search ( [id] INT NOT NULL , [name] NVARCHAR(255) , [description] NVARCHAR(MAX) , [content] NVARCHAR(MAX) , [priority] INT ) --searching in name field INSERT INTO #tbl_search ( [ID] , [name] , [description] , [content] , [priority] ) SELECT [ID] , [name] , [description] , [content] , 1 FROM [tbl_content] WHERE name LIKE '%' + @query + '%' --searching in description field INSERT INTO #tbl_search ( [ID] , [name] , [description] , [content] , [priority] ) SELECT [ID] , [name] , [description] , [content] , 2 FROM [tbl_content] WHERE description LIKE '%' + @query + '%' AND id NOT IN ( SELECT id FROM #tbl_search ) --..... SELECT * FROM #tbl_search ORDER BY [priority] DROP TABLE #tbl_search ```

Original source