Menu
I'm using MySQL 5.5 so that's why I can't use FULLTEXT search so please don't suggest it.
What I wanted to do is if user I have 5 records for example :
Amitesh
Ami
Amit
Abhi
Arun
and if someone searches for Ami then it should returns Ami first as exact match then Amit & Amitesh
233
When you use LIKE operator to search and fetch the matched results from the database, the records are selected based on their entry. On another hand, the ORDER BY keyword allows you to sort the result-set in ascending or descending order based on a specific column.
This is not permitted if the ONLY_FULL_GROUP_BY SQL_MODE is used." So you can't guarantee the order. You however use a function such as MAX(info) to get a specific value.
You should use this query instead for an exact match of the value PC: SELECT * FROM TransDetail TD WHERE TD. ModUserId = 'PC'; When using % in the WHERE clause you are using a wildcard that stands for 0 or more occurrences of characters in that position.
The order that rows are returned in is guaranteed ONLY by ORDER BY clause (or in MySQL, an ORDER BY implicitly specified in the GROUP BY clause.) Apart from that, there is NO GUARANTEE of the order rows will be returned in. Apart from that, MySQL is free to return the rows in any sequence.
You can do:
select *
from table t
where col like '%Ami%'
order by (col = 'Ami') desc, length(col);
SELECT *
FROM table t
WHERE col LIKE '%Ami%'
ORDER BY INSTR(col,'Ami') asc, col asc;
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
