如何用SQL实现先按名称字母排序，再按日期从新到旧排序？

Got it, let's get this sorted properly. Your original query has a couple of issues that are keeping it from meeting your requirements—let's break them down and fix it:

Key Issues in the Original Query #

• The ORDER BY clause only sorts by date ascending (oldest to newest), but you need to first sort by borrower name alphabetically, then by date newest to oldest.
• The GROUP BY clause is unnecessary here (unless you're trying to aggregate data, which your query doesn't indicate) and will likely cause errors in most SQL dialects, since you're selecting columns that aren't included in the GROUP BY or aggregated.

Corrected SQL Query #

SELECT 
    ME_BORROWER.ABN_BOR_NO, 
    ME_BORROWER.ABN_BOR_NAME, 
    ABN_ACCESS_NO, 
    ABN_LOAN_DATE 
FROM ME_BORROWER 
LEFT OUTER JOIN ME_LOAN 
    ON ME_BORROWER.ABN_BOR_NO = ME_LOAN.ABN_BOR_NO 
WHERE 
    ABN_TOWN IN ('Leicester', 'Hinkley')
ORDER BY 
    ME_BORROWER.ABN_BOR_NAME ASC,  -- Sort alphabetically by borrower name first
    ABN_LOAN_DATE DESC;             -- Then sort newest dates first for the same name

What Changed & Why #

• Simplified the WHERE clause: Replaced OR with IN for cleaner, more maintainable code when filtering multiple values.
• Removed unnecessary GROUP BY: Since you're not using aggregate functions (like COUNT, SUM), grouping here doesn't serve a purpose and can lead to inconsistent results or syntax errors.
• Adjusted ORDER BY:
  • First sorts by ABN_BOR_NAME in ascending order (A-Z) to meet your alphabetical requirement.
  • Then sorts by ABN_LOAN_DATE in descending order (newest to oldest) for records with the same borrower name.
• Explicit table prefixes: Added ME_BORROWER. to ABN_BOR_NAME to avoid ambiguity (in case both tables had a column with the same name).

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