1

Suppose I have a (code-generated) query that is looking for keywords in a number of different fields. For example, any one of the terms University, South or Africa might be in the name field, or the address field, or some other field. I want to find all records where any of these fields match any of these keywords:

SELECT *
FROM
    Institutions
WHERE
    Institutions.name LIKE '%University%' OR
    Institutions.address LIKE '%University%' OR
    Institutions.name LIKE '%South%' OR
    Institutions.address LIKE '%South%' OR
    Institutions.name LIKE '%Africa%' OR
    Institutions.address LIKE '%Africa%'

However, this is going to pull up everything. I would like to sort by the records that match the most possible conditions.

I think this would work but looks pretty clunky:

SELECT *
FROM
    Institutions
WHERE
    Institutions.name LIKE '%University%' OR
    Institutions.address LIKE '%University%' OR
    Institutions.name LIKE '%South%' OR
    Institutions.address LIKE '%South%' OR
    Institutions.name LIKE '%Africa%' OR
    Institutions.address LIKE '%Africa%'
ORDER BY
    (
        CASE WHEN Institutions.name LIKE '%University%' THEN 1 ELSE 0 END +
        CASE WHEN Institutions.address LIKE '%University%' THEN 1 ELSE 0 END +
        CASE WHEN Institutions.name LIKE '%South%' THEN 1 ELSE 0 END +
        CASE WHEN Institutions.address LIKE '%South%' THEN 1 ELSE 0 END +
        CASE WHEN Institutions.name LIKE '%Africa%' THEN 1 ELSE 0 END +
        CASE WHEN Institutions.address LIKE '%Africa%' THEN 1 ELSE 0 END
    ) DESC

Is there something cleaner? I'm fine with SQL Server-specific solutions but pure SQL solutions would be great.

Jordan Reiter
  • 20,467
  • 11
  • 95
  • 161

3 Answers3

0

You could use ranking with full-text search in sqlserver, but I think it is a bit overkill...

Anyway a few pointer:

http://technet.microsoft.com/en-us/library/ms142524%28v=sql.105%29.aspx

http://technet.microsoft.com/en-us/library/cc879245.aspx

Or if you create a function from the case-when thing which returns the count, you can use the same function in the where. (This is also hacky...)

Lajos Veres
  • 13,595
  • 7
  • 43
  • 56
0

Your solution looks okay to me, but here's something else that should work (pseudo-code):

SELECT * FROM
(SELECT *,COUNT(*) AS cnt FROM
(SELECT * FROM I
WHERE name LIKE '%etc%'
UNION ALL
SELECT * FROM I
WHERE name LIKE '%etc2%'
-- etc. )
GROUP BY *)
ORDER BY cnt DESC;
wvdz
  • 16,251
  • 4
  • 53
  • 90
  • Yeah, I thought about doing UNIONs + COUNT too but I don't think it's an improvement in terms of readability. I also wonder about execution time; I'm thinking that using case statements on already filtered data must be faster than a bunch of UNIONs. – Jordan Reiter Nov 20 '13 at 20:30
0

Maybe like this:

SELECT * FROM (
    SELECT i.*,
    (
      CASE WHEN i.name LIKE '%University%' THEN 1 ELSE 0 END +
      CASE WHEN i.address LIKE '%University%' THEN 1 ELSE 0 END +
      CASE WHEN i.name LIKE '%South%' THEN 1 ELSE 0 END +
      CASE WHEN i.address LIKE '%South%' THEN 1 ELSE 0 END +
      CASE WHEN i.name LIKE '%Africa%' THEN 1 ELSE 0 END +
      CASE WHEN i.address LIKE '%Africa%' THEN 1 ELSE 0 END
    ) AS rnk
    FROM Institutions i 
 ) result
 WHERE result.rnk > 0
 ORDER BY result.rnk DESC

EDIT: I forgot on Apply 

SELECT *
FROM
    Institutions i
CROSS APPLY
(
      SELECT
     (CASE WHEN i.name   LIKE '%University%' THEN 1 ELSE 0 END +
      CASE WHEN i.address LIKE '%University%' THEN 1 ELSE 0 END +
      CASE WHEN i.name    LIKE '%South%' THEN 1 ELSE 0 END +
      CASE WHEN i.address LIKE '%South%' THEN 1 ELSE 0 END +
      CASE WHEN i.name    LIKE '%Africa%' THEN 1 ELSE 0 END +
      CASE WHEN i.address LIKE '%Africa%' THEN 1 ELSE 0 END) as rnk
) c
ORDER BY c.rnk DESC

Small example

slavoo
  • 5,798
  • 64
  • 37
  • 39