logo
down
shadow

MySQL - Order By exact match at the end


MySQL - Order By exact match at the end

By : Sandy1729
Date : November 22 2020, 10:48 AM
To fix this issue you can use multiple values in the order by , the first clause will make the Other to appear at the end
code :
SELECT ID, SPORT
FROM Table1
ORDER BY SPORT='Other' , SPORT 


Share : facebook icon twitter icon
mysql query order by exact match with multiple LIKE clauses

mysql query order by exact match with multiple LIKE clauses


By : indrajeet mothe
Date : March 29 2020, 07:55 AM
wish of those help OK, i've been working with this for some time and I'm a little stuck. , Try this:
code :
(
  SELECT
      id, 
      IF( company_name IS NOT NULL AND company_name <> '', company_name, surname ) AS name,
      first_name, country, phone1, isowner, isholidayrenter, isproholidayrenter, 
      islongtermrenter, isprolongtermrenter, isprobuyer, isbuyer 
  FROM
      clients 
  WHERE
      id LIKE '$search' OR 
      surname LIKE '$search' OR 
      company_name LIKE '$search'
)
union all
(
  SELECT 
      id,
      IF( company_name IS NOT NULL AND company_name <> '', company_name, surname ) AS name, 
      first_name, country, phone1, isowner, isholidayrenter, isproholidayrenter,
      islongtermrenter, isprolongtermrenter, isprobuyer, isbuyer 
  FROM
      clients 
  WHERE
      ( id LIKE '%$search%' AND id NOT LIKE '$search' ) OR 
      ( surname LIKE '%$search%' AND SURNAME NOT LIKE '$search' ) OR
      ( company_name LIKE '%$search%' AND company_name NOT LIKE '$search' )
)
LIMIT $start, $limit;
Mysql LIKE Order Bu Exact Match

Mysql LIKE Order Bu Exact Match


By : maratshi
Date : March 29 2020, 07:55 AM
Hope that helps I have a query that gets information using the LIKE , try this
code :
    SELECT * FROM table1 WHERE column LIKE '%$search%'
    order by case when column like '$search%'  then 1        //hello in begining
                  when column like '%$search%' then 2        //hello in middle
                  when column like '%$search'  then 3 end    //hello in end
How to use MySQL like with order by exact match first

How to use MySQL like with order by exact match first


By : Thamayanthi
Date : March 29 2020, 07:55 AM
this will help I'm using MySQL 5.5 so that's why I can't use FULLTEXT search so please don't suggest it. , You can do:
code :
select *
from table t
where col like '%Ami%'
order by (col = 'Ami') desc, length(col);
Order list with weighting for exact match then lose match in elastic search

Order list with weighting for exact match then lose match in elastic search


By : LiangYu Lin
Date : March 29 2020, 07:55 AM
MySQL Full-Text Search- Search words with exact match & wildcard but get exact match first other match

MySQL Full-Text Search- Search words with exact match & wildcard but get exact match first other match


By : user3043687
Date : December 01 2020, 04:40 PM
may help you . This query ranks records with the whole word higher, but car,cards ranks the same as just car so it is still not optimal.
code :
SELECT id, search_tags, 
    MATCH(search_tags) AGAINST('car *') + 
    MATCH(search_tags) AGAINST('car') AS relevance_rank
FROM images
WHERE MATCH(search_tags) AGAINST('car *') 
OR MATCH(search_tags) AGAINST('car')
ORDER BY 3 DESC
+----+---------------------+----------------+
| id |     search_tags     | relevance_rank |
+----+---------------------+----------------+
| 12 | car,cards,food,code | 0.1677478002   |
| 13 | car,water,sky       | 0.1677478002   |
| 11 | carrier,food,drink  | 0.0093915509   |
+----+---------------------+----------------+
SELECT id, search_tags, 
    MATCH(search_tags) AGAINST('car *') + 
    MATCH(search_tags) AGAINST('car') AS relevance_rank,
    LENGTH(search_tags) - 
    LENGTH(REPLACE(search_tags, 'car', space(LENGTH('car')) -1 )) AS occurences
FROM images
WHERE MATCH(search_tags) AGAINST('car *') 
OR MATCH(search_tags) AGAINST('car')
ORDER BY 3 DESC, 4 DESC
+----+---------------------+----------------+------------+
| id |     search_tags     | relevance_rank | occurences |
+----+---------------------+----------------+------------+
| 12 | car,cards,food,code | 0.1677478002   |          2 |
| 13 | car,water,sky       | 0.1677478002   |          1 |
| 11 | carrier,food,drink  | 0.0093915509   |          1 |
+----+---------------------+----------------+------------+
Related Posts Related Posts :
  • select the records with the highest amount
  • Changing Mysql golbal variable
  • How to create Effective Indexing on MySQL
  • `require': Incorrect MySQL client library version
  • How to save a FirstName of a Person efficiently without duplicate entries
  • Can I store my own sql query by mysql special function and execute stored query later?
  • MySQL comma separated field query
  • Why does the same exact query produce 2 different MySQL explain results?
  • Suppress warning output in bash
  • Doctrine "on update CURRENT_TIMESTAMP" annotation (Symfony2)
  • Designing "Friend" relationships across two mysql tables
  • Importing sql file using phpmyadmin in EasyPHP
  • Easy PHP and mysql ..php cannot see database on local pc (all works on server)
  • MySQL join optimization - bypassing filesort
  • How to Select record with the maximum value in a GROUP BY?
  • MySQL Innodb Full text Proximity Search Gives Horrible Performance
  • MySQL best approach for db normalising, relationships and foreign keys
  • How to update one table based on the count value of another table while matching ids?
  • MAMP Pro 5.1.1-terminates MySQL when quitting
  • SQL - Get date of most votes cast
  • "Fuzzier" search in mysql
  • Tracing mysqld Using SystemTap
  • SELECT + INSERT + Query Cache = MySQL lock up
  • SQL vs. NoSQL for medium complexity search systems
  • SQL Error when creating table
  • Is it possible to write a query to compare rows to other rows in same table?
  • Display all duplicate records based multiple columns
  • summing time column in mysql
  • InnoDB: Cannot open table
  • Why is IN() considered O(logN) operation?
  • Can a deadlock occur on commit?
  • Is there a fast way to do a '%phrase%' search in any DB?
  • Is using an IN over a huge data set a good idea?
  • How can I know if a table is already arranged in ascending order in MySQL?
  • MySQL - Designing database for a simple game
  • Why am I getting "Host '192.168.1.220' is not allowed to connect to this MySQL server"?
  • what type of password hash? possible mysql hash?
  • MySQL - Using a date range vs functions
  • Using IS NULL and COALESCE in OrderBy Doctrine Querybuilder
  • How to merge MySQL columns into one column if there are empty values in the table?
  • Cardinality Confusion
  • Renaming column multiple ids in SELECT
  • Group all from and to messages per user
  • How to handled the null values in WHERE IN condition in mysql
  • Is it okay to update primary key field to shift a record to the top?
  • Stop query through pdo
  • How to check whether particular value in field in MySQL exists
  • Execute a statement for every table in a database
  • insertion 74G data into mysql table cost more then 2 days, how to improve insert performance
  • Where can I see the differents "Hibernate Console Configurations" in Eclipse
  • MySql | relational database vs non relational database in terms of Performance
  • Exporting mysql database using mysqldump including procedures
  • Trigger MySQL not working
  • How do I update values for individual rows quickly?
  • MySQL join query with multiple where condition
  • MySQL stored procedure is not working
  • Select max date or null date from same table MySQL
  • Best solution workaround for needing a mysql event on insert?
  • MySQL select several columns of several tables at the same time without using JOIN
  • Is it more efficient to have a table with one of its columns mostly empty or make a new table and refer to it?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org