Member Avatar for jay.barnes

Good morning/afternoon/evening!

I'm trying to run a MySQL query, but it seems to be ignoring the "ORDER BY" clause, most likely, I'm believing, because I'm using the "IN" keyword in my query (several times, as a matter of fact, along with a "LIKE"). Is there a way I can structure my query, so that it will use the "ORDER BY" clause? I've included an example of the query below:

SELECT * FROM Ships, Accounts WHERE Available_Loads.Action != 'D'
AND Ships.Username = Accounts.Username
AND Ships.Username != 'demo'
AND Ships.Status != 'inactive'
AND Ships.PU_Date >= CURRENT_DATE()
AND Ships.Shipment_Type LIKE '%'
AND Ships.Required_Equipment IN ('AC', 'DD')
AND Ships.Origin_Country IN ('USA')
AND Ships.Origin_State IN ('TX')
AND Ships.Origin_City IN ('ALLEN', 'ARGYLE', 'ARLINGTON', 'AUBREY', 'BEDFORD', 'CARROLLTON', 'CEDAR HILL', 'CELINA', 'COLLEYVILLE', 'COPPELL', 'DALLAS', 'DENTON', 'DESOTO', 'DUNCANVILLE', 'EULESS', 'FLOWER MOUND', 'FORT WORTH', 'FRISCO', 'GARLAND', 'GRAND PRAIRIE', 'GRAPEVINE', 'HURST', 'IRVING', 'KELLER', 'LAKE DALLAS', 'LAVON', 'LEWISVILLE', 'LITTLE ELM', 'MCKINNEY', 'MELISSA', 'MESQUITE', 'NORTH RICHLAND HILLS', 'PLANO', 'PRINCETON', 'PROSPER', 'RICHARDSON', 'ROANOKE', 'ROWLETT', 'SOUTHLAKE', 'SUNNYVALE', 'THE COLONY', 'WYLIE')
AND Ships.Dest_Country IN ('USA')
AND Ships.Dest_State IN ('LA') ORDER BY 'PU_Date ASC'

Thanks for any suggestions you can provide!

  • 3 Contributors
  • 6 Replies
  • 478 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by wilch

Dani AI

Generated

Brief summary and root cause: the ORDER BY appeared to be ignored because the column name and sort direction were placed inside single quotes, turning them into a string literal. Ordering by a constant has no effect. As and pointed out, removing the quotes fixed the behavior and confirmed the query returned the expected order.

Key points and a corrected pattern:

  • Single quotes denote string literals in MySQL. Backticks (`) quote identifiers. Double quotes are treated as identifiers only when the SQL mode includes ANSI_QUOTES.
  • Prefer explicit JOINs, qualify column names, and avoid SELECT * when debugging ordering/ambiguity.
  • Correct ordering examples: ORDER BY PU_Date ASC or ORDER BY Ships.PU_Date DESC (no single quotes).

Example corrected query (same logic, cleaned up and qualified):

SELECT s.id, s.PU_Date, a.Username
FROM Ships AS s
JOIN Accounts AS a ON s.Username = a.Username
WHERE s.Username <> 'demo'
  AND s.Status <> 'inactive'
  AND s.PU_Date >= CURRENT_DATE()
  AND s.Required_Equipment IN ('AC','DD')
  AND s.Origin_State = 'TX'
  AND s.Dest_State = 'LA'
ORDER BY s.PU_Date ASC;

Troubleshooting and best practices:

  • Verify every referenced table/column is actually in the FROM/JOIN list (the original snippet referenced Available_Loads.Action but that table wasn't shown).
  • ORDER BY inside subqueries may be ignored by the optimizer unless paired with LIMIT; the top-level ORDER BY determines final output order.
  • For large result sets, an index on the ordering column reduces filesorts; using functions on the sort column prevents index use.
  • Qualify ambiguous column names when joins are involved to avoid the wrong column being used in ORDER BY.

Recommended Answers

All 6 Replies

Member Avatar for wilch 
AND Ships.Dest_State IN ('LA') ORDER BY 'PU_Date ASC'

In this last line, you have i think mistakenly put the keyword ASC as part of the column name. Try removing the ASC from the quotes !

Member Avatar for almostbob 
AND Ships.Dest_State IN ('LA') ORDER BY 'PU_Date ASC'

Try removing the ASC from the quotes !

good catch, stared at it and didnt see

commented: Thanks for the help with my MySQL query issue! +1
Member Avatar for jay.barnes

good catch, stared at it and didnt see

I'll give it a shot, but what if I want to specify the ORDER BY clause to be ascending or descending?

Member Avatar for wilch 
...AND Ships.Dest_State IN ('LA') ORDER BY 'PU_Date' ASC

The ASC is out of the quotes, what's in the quotes is the column name !

Don't forget to mark as solved, if solved ;)

Member Avatar for jay.barnes

Ah, got it, thanks!

I ended up having to remove the single quotes from the "ORDER BY" clause entirely to get it to work.

Thank you for the guidance and advice, guys! I appreciate it!

Member Avatar for wilch

Welcome brother !

commented: Thanks for the help with my MySQL query issue! +1
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.