Filter Specific Column Based on Search Results

Question

I have a search bar and an HTML table that is populated from my sql server database. Whenever I search, it uses a LIKE command on SKU and SKU Description to filter the table. When the results are displayed, I want it to filter the results based on what matches the SKU column the most.

For example, if I search for "100", it will display results where that are located in either the SKU or SKU Description columns. BUT, I want it to filter so that the values matched in the SKU column are filtered to be seen first before the values that are matched in the SKU Description column.

I assume it's just a matter of adding something to my query, but how would I do this?

Here is my query:

$query = "SELECT Product_ID, [Major Category], [Minor Category], [Product Report Code], SKU, [SKU Description], [SKU Status], CAST([Create Date] AS DATE) AS Date, Group_ID, [SKU Group]
          FROM vProducts
          WHERE CONCAT(SKU, [SKU Description])
          LIKE '%".$valueToSearch."%'";

Answer 1

This may be overkill, and you'll have to format this to your "$query". Here's the sql.

SELECT A.Product_ID, A.[Major Category], A.[Minor Category], A.[Product Report Code], A.SKU, A.[SKU Description], A.[SKU Status], A.Date, A.Group_ID, A.[SKU Group], A.Sort
FROM (
        SELECT Product_ID, [Major Category], [Minor Category], [Product Report Code], SKU, [SKU Description], [SKU Status], CAST([Create Date] AS DATE) AS Date, Group_ID, [SKU Group], 1 AS Sort
        FROM vProducts
        WHERE SKU LIKE '%' + @value + '%'

        UNION all

        SELECT Product_ID, [Major Category], [Minor Category], [Product Report Code], SKU, [SKU Description], [SKU Status], CAST([Create Date] AS DATE) AS Date, Group_ID, [SKU Group], 2 AS Sort
        FROM vProducts
        WHERE [SKU Description] LIKE '%' + @value + '%'
    ) A
ORDER BY A.Sort