CODEWITHSUNDEEP
sqlsql-serverdistinctunion
Levon
Levon

Reputation: 159

order union but keep distinct

how to query a table Store(ID,Title,Company) to get all the records where title contains "term" showing fully matched records first then partial matches. in other words if im looking for "foo" i wand records displayed as

Heres what i have

Create PROCEDURE [dbo].[SearchUserInfo] 
@query nvarchar(500),
@term nvarchar(500)

(SELECT Title,Company,
1 as match
FROM Stores 
WHERE CONTAINS(Title, @query))

UNION 

(SELECT Title,Company,
0 as match
FROM Stores 
WHERE Title like '%' + @term +'%')

order by match desc

the problem with this query is that it returns duplicate records. if i remove 0 as match tehn i dont know how to sort so results from CONTAINS will appear before results from LIKE.

thanks

Upvotes: 2

Views: 254

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you don't want to use full text search, you can do this with a single query:

select title, company,
       (case when MatchType like 'FULL%' then 1 else 0 end) as Match
from (select title, company,
             (case when title like concat(@query, ' %') then 'FULL START'
                   when title like concat('% ', @query) then 'FULL END'
                   when title like concat('% ', @query, ' %') then 'FULL MIDDLE'
                   when title like concat(@query, '%') then 'PARTIAL START'
                   when title like concat('%', @query) then 'PARTIAL END'
                   when title like concat('%', @query, '%') then 'PARTIAL MIDDLE'
                   else 'NONE'
              end) as MatchType
      from Stores
     ) s
where MatchType <> 'NONE'
order by MatchType

This may not quite give you the flexibility you want in terms of delimiters on the words. But, it does give you more control over the nature of the matching and resulting ordering.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453278

You can combine the two result sets then use GROUP BY to remove the duplicates and ORDER BY MAX(match) DESC to place the groups returned by CONTAINS first

;WITH CTE
     AS (SELECT Title,
                Company,
                1 AS match
         FROM   Stores
         WHERE  CONTAINS(Title, @query)
         UNION ALL
         SELECT Title,
                Company,
                0 AS match
         FROM   Stores
         WHERE  Title LIKE '%' + @term + '%')
SELECT Title,
       Company,
FROM   CTE
GROUP  BY Title,
          Company
ORDER  BY MAX(match) DESC

Upvotes: 2

Related Questions