SQL自定义排序问询：拉丁字母优先及分组内多字段排序

Got it, let's tackle these two SQL sorting questions one by one—they're common scenarios when dealing with multilingual data, so I'll break down solutions for major databases since syntax varies a bit.

The approach here depends on your database system, since collation rules and character handling differ across platforms. Here are practical solutions for the most widely used SQL databases:

MySQL/MariaDB #

You have two main options: use a collation that prioritizes Latin characters, or explicitly categorize characters with a conditional sort.

• Collation method: If your column uses UTF-8, try a collation like utf8mb4_latvian_ci (it natively orders Latin before Cyrillic). Just append COLLATE to your sort clause:

  SELECT your_text_column
  FROM your_table
  ORDER BY your_text_column COLLATE utf8mb4_latvian_ci ASC;
  

• Conditional sort (more control): If collations don't fit, use a regex to check if the first character is Latin:

  SELECT your_text_column
  FROM your_table
  ORDER BY
    CASE WHEN your_text_column REGEXP '^[a-zA-Z]' THEN 0 ELSE 1 END,
    your_text_column ASC;
  

PostgreSQL #

PostgreSQL uses regex with the ~ operator. You can sort by a conditional flag first, then the text itself:

SELECT your_text_column
FROM your_table
ORDER BY
  CASE WHEN your_text_column ~ '^[a-zA-Z]' THEN 0 ELSE 1 END,
  your_text_column ASC;

If you need to ignore accents, enable the unaccent extension first, then modify the sort to use unaccent(your_text_column).

SQL Server #

Use the ASCII() function to check if the first character falls within the Latin alphabet range:

SELECT your_text_column
FROM your_table
ORDER BY
  CASE WHEN ASCII(LEFT(your_text_column, 1)) BETWEEN 65 AND 90 
          OR ASCII(LEFT(your_text_column, 1)) BETWEEN 97 AND 122 
       THEN 0 ELSE 1 END,
  your_text_column ASC;

Alternatively, use a collation like SQL_Latin1_General_CP1_CI_AS if it aligns with your sorting needs.

For your requirement—sorting first by SKU, acnumber, year, then within each sku+acnumber+year group sorting Customer by Latin first, then Cyrillic—you just chain the outer sort conditions with the group-level conditional logic from the first question.

Here's the full query for each database:

MySQL/MariaDB #

SELECT customer, sku, stuff, action, acnumber, year
FROM mytable
ORDER BY
  -- Outer sort: SKU, acnumber, year ascending
  sku ASC,
  acnumber ASC,
  year ASC,
  -- Group-level sort: Prioritize Latin-named customers first
  CASE WHEN customer REGEXP '^[a-zA-Z]' THEN 0 ELSE 1 END,
  customer ASC;

PostgreSQL #

SELECT customer, sku, stuff, action, acnumber, year
FROM mytable
ORDER BY
  sku ASC,
  acnumber ASC,
  year ASC,
  CASE WHEN customer ~ '^[a-zA-Z]' THEN 0 ELSE 1 END,
  customer ASC;

SQL Server #

SELECT customer, sku, stuff, action, acnumber, year
FROM mytable
ORDER BY
  sku ASC,
  acnumber ASC,
  year ASC,
  CASE WHEN ASCII(LEFT(customer, 1)) BETWEEN 65 AND 90 
          OR ASCII(LEFT(customer, 1)) BETWEEN 97 AND 122 
       THEN 0 ELSE 1 END,
  customer ASC;

Quick Notes: #

• If Customer values have leading non-alphabetic characters (like numbers or symbols), adjust the regex/ASCII checks. For example, change the regex to ^[a-zA-Z].* to target the first alphabetic character instead of the very first character.
• If a native collation works for your Customer sort, you can simplify the group-level part to customer COLLATE [your-collation] instead of the CASE statement—this is cleaner if it meets your needs.

内容的提问来源于stack exchange，提问作者psysky