How to filter search results in this example

Question

I have a product table that contains thousands of products. Some products are available in different colors. But when somebody searches for example 'mp3 player' i dont want to show him every color, instead just the player with the best selling color.

Her is the table layout (simplified):

ID | PRODUCT_NAME      | COLOR | SALE_COUNT
===========================================
 1 | Mp3 player red    | red   | 2
 2 | Mp3 player gold   | gold  | 1
 3 | Mp3 player black  | black | 100

But, when a user searches for 'Mp3 player red' i want to show him the red player, instead of the black one. The search is performed using the 'like' operator (yeah, i know lucene, anyway i need to solve this).

Any suggestions how to solve this issue? i have a few ideas but none of them seems to be a good solution. thanks,

postgreSQL db and jave is used to create the result.

Answer 1

Just order by the SALE_COUNT descending and select only the first one, if I understand your problem:

SELECT TOP 1 ID, PRODUCT_NAME, COLOR, SALE_COUNT
FROM table
WHERE PRODUCT_NAME LIKE '%' + @searchParam + '%'
ORDER BY SALE_COUNT DESC

Be sure to sanitize the search parameter first by removing any possible sql injection attacks.

EDIT:

If you're using postgres, the syntax is to put a "LIMIT n" at the end, so:

SELECT ID, PRODUCT_NAME, COLOR, SALE_COUNT
FROM table
WHERE PRODUCT_NAME LIKE '%' + @searchParam + '%'
ORDER BY SALE_COUNT DESC
LIMIT 1

Answer 2

it will be something like

SELECT TOP 1 (*) FROM table
WHERE PRODUCT_NAME LIKE 'mp3 player %' 
ORDER BY SALE_COUNT DESC

So, we are selecting only the top row, ordered, descending by SALE_COUNT (so that the highest sale count is on the top)

Answer 3

SELECT  *
FROM    products
WHERE   product_name LIKE 'mp3 player%'
ORDER BY
        sale_count DESC
LIMIT 1