mysql顺序排列查找前十项,MySQL按字母顺序显示结果，但在按字母顺序排列的列表之前显示特定项目...

I have a list in MySQL that is sorted in alphabetical order as follows.

Argentina

Brazil

China

Malta

USA

Since the magority of my customers are from the USA, I want it to be displayed first in the list e.g.

USA

Argentina

Brazil

China

Malta

This is my code -

$sqlprimaryCategory = $dbh->prepare("SELECT * FROM $tableName GROUP BY primary_category ");

This is what I tried but did not work -

$sqlprimaryCategory = $dbh->prepare("SELECT * FROM $tableName GROUP BY primary_category ORDER BY primary_category='USA' ASC, primary_category ASC");

解决方案

just do a conditional order by with a case statement like so

QUERY:

SELECT *

FROM $tableName

GROUP BY primary_category

ORDER BY

CASE primary_category WHEN 'USA' THEN 1 ELSE 2 END ASC,

primary_category ASC

EDIT:

if you want to order by multiple fields first and then the rest you can do it like this.

SELECT *

FROM $tableName

GROUP BY primary_category

ORDER BY

CASE primary_category

WHEN 'USA' THEN 1 --#-- 1 for usa

WHEN 'China' THEN 2 --#-- 2 for china

ELSE 3 END ASC, --#-- 3 for anything else

primary_category ASC