Please help me to create a select query which contains 10 'where' clause and the order should be like that: the results should be displayed in order of most keywords(where conditions) matched down to least matched.
NOTE: all 10 condition are with "OR".
Please help me to create this query. i am using ms-sql server 2005
Like:
Select *
from employee
where empid in (1,2,4,332,434)
or empname like 'raj%'
or city = 'jodhpur'
or salary >5000
In above query all those record which matches maximum conditions should be on top and less matching condition record should be at bottom.
SELECT name, credit_limit FROM customers ORDER BY 2 DESC, 1; In this example, the position of name column is 1 and credit_limit column is 2. In the ORDER BY clause, we used these column positions to instruct the Oracle to sort the rows.
You can use the WHERE clause with or without the ORDER BY statement.
Does a join order matter in SQL Server? Strictly speaking, no, the join order does not matter. The query optimizer will rearrange the order of the joins into what it considers to be the best order.
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort that column should be in the column-list.
SELECT *
FROM (SELECT (CASE WHEN cond1 THEN 1 ELSE 0 END +
CASE WHEN cond2 THEN 1 ELSE 0 END +
CASE WHEN cond2 THEN 1 ELSE 0 END +
...
CASE WHEN cond10 THEN 1 ELSE 0 END
) AS numMatches,
other_columns...
FROM mytable
) xxx
WHERE numMatches > 0
ORDER BY numMatches DESC
EDIT: This answer was posted before the question was modified with a concrete example. Marcelo's solution addresses the actual problem. On the other hand, my answer was giving priority to matches of specific fields.
You may want to try something like the following, using the same expressions in the ORDER BY
clause as in your WHERE
clause:
SELECT *
FROM your_table
WHERE field_1 = 100 OR
field_2 = 200 OR
field_3 = 300
ORDER BY field_1 = 100 DESC,
field_2 = 200 DESC,
field_3 = 300 DESC;
I've recently answered a similar question on Stack Overflow which you might be interested in checking out:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With