How to sort based on keyword search?

How to sort based on keyword search?

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 by Justin K for How to sort based on keyword search?

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 by Blorgbeard for How to sort based on keyword search?

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 by Brian Hooper for How to sort based on keyword search?

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 by Brock Adams for How to sort based on keyword search?

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 by NanoHead for How to sort based on keyword search?

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

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72