How to sort based on keyword search?

Question

My sql statement

SELECT *  
  FROM mytable 
 WHERE (CONTAINS(keyword,' "green" '))

How do I list the top matched result like this

Green
Army Green
Hunter Green

instead of the below

Army Green
Green
Hunter Green

I know that we can't use the order by asc or desc with my case.

I plan to add a space before 'green' and use the dictionary object or insert into the database and sort it alpha order. I think there is a better technique out here that can do the job instead of using my slow technique specially dealing with more than multi-million records in the keyword database.

By the way, we are still using the ancient SQL Server 2000.

this is the keywords which is related to green. I like to get the top matched result based on the input keyword. The result should show the closest match to the keyword.

Data in the database

Army Green
Blue-Green
Bright Green
British Racing Green
Camouflage Green
Dark Green
Dark Pastel Green
Electric Green
Fern Green
Forest Green
Green
Green-Yellow
Hunter Green
India Green
Islamic Green
Jungle Green
Kelly Green
Lawn Green
Lime Green
Moss Green
MSU Green
Office Green
Pastel Green
Persian Green
Pine Green
Sea Green
Shamrock Green
Spring Green
Tea Green
Yellow-Green

Answer 1

Well, you can special-case exact matches:

SELECT *  
  FROM mytable 
 WHERE keyword = 'green'
UNION ALL
SELECT *  
  FROM mytable 
 WHERE (CONTAINS(keyword,' "green" '))
   AND keyword <> 'green'

Answer 2

I'm not sure exactly what you're after, and I'm not very familiar with full text search, but perhaps you could just order by len(keyword).

That would work for your example, anyway. It wouldn't prioritize lines with more than one instance of "green" though.

Answer 3

Perhaps...

SELECT *  
    FROM mytable 
    WHERE (CONTAINS(keyword,' "green" '))
    ORDER BY FIND_IN_SET('green', keyword);

Find_in_set MySQL though; you may not have it. It just returns the position of the word 'Green' in the keyword column.

SQL Server appears to have CHARINDEX('green', keyword) if that's any help.

Answer 4

It looks like you are trying to find the frequency of "green" keywords?

Suppose you your table(s) looked like this (Provide the full, exact description.):

CREATE TABLE mytable (item VARCHAR(40), keyword VARCHAR(100) )

INSERT INTO mytable (item, keyword)
SELECT      'room 01', 'Green'              UNION ALL
SELECT      'room 02', 'Green'              UNION ALL
SELECT      'room 03', 'Green'              UNION ALL
SELECT      'room 04', 'Army Green'         UNION ALL
SELECT      'room 05', 'Army Green'         UNION ALL
SELECT      'room 06', 'Hunter Green'       UNION ALL
SELECT      'room 07', 'Some Other Color'

.

Then the desired results are obtained by:

SELECT      keyword, COUNT (item) AS cnt
FROM        mytable
WHERE       keyword LIKE '%green%'
GROUP BY    keyword 
ORDER BY    cnt DESC

/*
Yields:
    keyword         cnt
    ------------    ---
    Green           3
    Army Green      2
    Hunter Green    1
*/

.

Or, if full text indexing is in use:

SELECT      keyword, COUNT (item) AS cnt
FROM        mytable
WHERE       (CONTAINS(keyword,' "green" '))
GROUP BY    keyword 
ORDER BY    cnt DESC

.
If keyword is a text or ntext column:

SELECT      CAST (keyword as VARCHAR(8000)), COUNT (item) AS cnt
FROM        mytable
WHERE       (CONTAINS(keyword,' "green" '))
GROUP BY    CAST (keyword as VARCHAR(8000))
ORDER BY    cnt DESC

Answer 5

I have came up with a solution by using the the PATINDEX and with CASE function to create the fake column and sort it out from the fake column and it is working fine.

Thanks guys