In MySQL is there a way we can order the results in order of set of rules also used for filtering the results?
E.g. if we have a name field and input from the user then I would like to filter and sort the records as following
If we match the input against the name field
- Exact Match
- 1st keyword of input exact match for name
- 2nd keyword of input exact match for name
- Name starts with 1st keyword
- Name starts with 2nd keyword
I would like to filter the results based on the above mentioned cases and order them in above listed sequence.
Input : two three
Data :
| one |
| two |
| three |
| one two three |
| one three two |
| three one two |
| two three one |
Output :
| two |
| three |
| two three one |
| three one two |
Full Text Search might a possible solution but from my past experience it does not always give an expected relevancy value to exactly order in the above mentioned manner.
