I am querying an Access DB based on a multiple field search, some of which using the LIKE '%%' function and i would like to know if there's a way of returning the results ORDERED by the best matches first... the search itself is optional conditional which is why i'm having trouble with the result set.
SELECT * FROM Table WHERE (RequestID>=1 AND RequestID<=20000) OR SubmitterName LIKE '%MaxOvrdrv%' OR DirectorName LIKE '%MyDirector%' OR AssignedTo='MaxOvrdrv'
That query will return me 20 000 results or more because of the requestID query, but i would like to have the results that are within 20 000, and match the most of the other fields queried first... example, i would like to have:
20 222 - MaxOvrdrv - MyDirector - MaxOvrdrv
result come up FIRST instead of
1 - jamie - julie - jamie
which only matches 1 search criteria instead of 4 or 3 or 2...
So is there a way to do that directly into the SQL Query??