CODEWITHSUNDEEP
sql-serversearch-engine
Sasha
Sasha

Reputation: 20854

Search query, 'order by' priority

I need implement simple search in small content table: id, name, description, content. Results must be order by priorities

  1. name
  2. description
  3. 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

Upvotes: 5

Views: 13646

Answers (3)

hpaknia
hpaknia

Reputation: 3118

For sake of completeness, I move @Amirshk brilliant solution to the next level by using the case when in order clause (query for mysql):

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

Upvotes: 0

Amirshk
Amirshk

Reputation: 8258

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

Upvotes: 9

Alex Shirshov
Alex Shirshov

Reputation: 655

The easiest way would be to use UNION query:

SELECT name AS text FROM tbl_content WHERE name LIKE '%' + @query + '%'
UNION
SELECT description AS text FROM tbl_content WHERE desription LIKE '%' + @query + '%'
UNION
SELECT content AS text FROM tbl_content WHERE content LIKE '%' + @query + '%'

Upvotes: 2

Related Questions