SQL ORDER BY Explained — Sort Your Results Like a Pro
⭐ Want to learn SQL step-by-step? Check out our completely FREE Hands-On Practice 👉 SQL course for beginners .
The SQL
ORDER BY clause controls the order of rows returned by your query. Sorting results is one of the most common tasks in SQL from ranking top products to ordering timestamps for reports. In this beginner-friendly guide you'll learn how to:
- • Sort ascending and descending
- • Sort by multiple columns
- • Handle
NULLvalues when sorting - • Use expressions in
ORDER BY - • Understand performance considerations
📽 Watch the Video Tutorial | See the ORDER BY Clause in Action!
Basic ORDER BY — Ascending and Descending
The simplest use of
ORDER BY sorts rows by one column. By default, sorting is ascending.
-- Ascending (default) SELECT FirstName, LastName, CreatedAt FROM Users ORDER BY CreatedAt;
-- Descending SELECT FirstName, LastName, CreatedAt FROM Users ORDER BY CreatedAt DESC;
How these queries work:
- SELECT FirstName, LastName, CreatedAt: Displays the user's first name, last name, and account creation date.
- FROM Users: Retrieves the records from the Users table.
- ORDER BY CreatedAt: Sorts the results by the CreatedAt column in ascending order, from the oldest date to the newest.
- DESC: Changes the sorting direction to descending, displaying the newest dates first.
Example: If users were created on January 5, January 10, and January 15, ascending order displays January 5 first, while descending order displays January 15 first.
Use
DESC to reverse the default (largest/most recent first). If you want the smallest or oldest first, use ascending (or omit DESC).
Multi-Column Sorting
You can pass multiple columns to
ORDER BY. SQL sorts by the first column, then breaks ties using the second, and so on.
SELECT ProductName, Category, Price, Rating FROM Products ORDER BY Category ASC, Rating DESC, Price ASC;
How this query works:
- SELECT ProductName, Category, Price, Rating: Displays the product name, category, price, and rating.
- FROM Products: Retrieves product records from the Products table.
- ORDER BY Category ASC: Sorts products alphabetically by category.
- Rating DESC: Within each category, places products with the highest ratings first.
- Price ASC: If two or more products have the same category and rating, sorts those products by price from lowest to highest.
Example: If three products belong to the Electronics category, products with a rating of 5 appear before products rated 4. If two products both have a rating of 5, the less expensive product appears first.
In this example results are sorted by
Category. Within each category, higher-rated items come first; for equal ratings, cheaper items come first.
Practical Example — Sort by Date and Priority
SELECT TicketID, Priority, CreatedAt FROM SupportTickets ORDER BY Priority DESC, CreatedAt ASC;
How this query works:
- SELECT TicketID, Priority, CreatedAt: Displays each support ticket's ID, priority, and creation date.
- FROM SupportTickets: Retrieves ticket records from the SupportTickets table.
- ORDER BY Priority DESC: Sorts tickets by priority in descending order. This works as intended when higher-priority values sort ahead of lower-priority values, such as numeric priorities where 3 is more urgent than 2.
- CreatedAt ASC: Within each priority level, displays the oldest tickets first.
Example: If two tickets have the same priority, the ticket created first appears before the newer ticket.
This ensures urgent tickets appear before lower priority ones; within the same priority, older tickets appear first so they aren’t forgotten.
📽 Watch the Video Tutorial | See the ORDER BY Clause in Action!
Sorting by Expressions and Aliases
You can sort by expressions, functions, and aliases (note: alias support may vary slightly by SQL dialect):
-- Sort by calculated column (alias) SELECT OrderID, Quantity * UnitPrice AS LineTotal FROM OrderLines ORDER BY LineTotal DESC; -- Sort by function result (SQL Server) SELECT UserID, LEN(Username) AS NameLen FROM Users ORDER BY LEN(Username) ASC;
How these queries work:
- SELECT OrderID: Displays the ID of each order line.
- Quantity * UnitPrice: Multiplies the quantity ordered by the unit price to calculate the line total.
- AS LineTotal: Gives the calculated amount the column name LineTotal.
- ORDER BY LineTotal DESC: Sorts the results by the calculated line total, from highest to lowest.
- LEN(Username): Calculates the number of characters in each username. In SQL Server, LEN() excludes trailing spaces.
- AS NameLen: Names the calculated character count NameLen.
- ORDER BY LEN(Username) ASC: Sorts usernames from the shortest to the longest based on their character counts.
Example: If one order line has a quantity of 5 at $20 each, its LineTotal is $100. The first query sorts that order line according to its calculated total.
Using expressions is helpful for ranking derived values without creating a temporary column.
Handling NULLs in ORDER BY
Different databases sort
NULL differently. In many systems, NULL sorts first in ascending order, but behavior varies. You often need to control how NULLs appear.
PostgreSQL Example:
SELECT Name, LastLogin FROM Users ORDER BY LastLogin DESC NULLS LAST;
How this query works:
- SELECT Name, LastLogin: Displays each user's name and last login date.
- FROM Users: Retrieves user records from the Users table.
- ORDER BY LastLogin DESC: Sorts login dates from newest to oldest.
- NULLS LAST: Places records with a NULL LastLogin value at the bottom of the results.
MySQL Example:
SELECT Name, LastLogin FROM Users ORDER BY (LastLogin IS NULL), LastLogin DESC;
How this query works:
- SELECT Name, LastLogin: Displays each user's name and last login date.
- LastLogin IS NULL: Checks whether the LastLogin value is NULL. MySQL returns 1 when it is NULL and 0 when it is not.
- ORDER BY (LastLogin IS NULL): Sorts the non-NULL values first because 0 comes before 1.
- LastLogin DESC: Within the non-NULL records, sorts the most recent login dates first.
SQL Server Example:
SELECT Name, LastLogin FROM Users ORDER BY CASE WHEN LastLogin IS NULL THEN 1 ELSE 0 END, LastLogin DESC;
How this query works:
- CASE WHEN LastLogin IS NULL: Checks whether a user's last login date is missing.
- THEN 1 ELSE 0: Assigns 1 to NULL values and 0 to dates that contain a value.
- ORDER BY CASE: Sorts records with actual dates first and NULL values last.
- LastLogin DESC: Sorts the actual login dates from newest to oldest.
Using
NULLS FIRST or NULLS LAST (supported in PostgreSQL and Oracle) makes your ordering explicit and prevents surprises. SQL Server requires an alternative approach, such as the CASE expression shown above.
Collation & Case Sensitivity
Sorting textual data is influenced by collation (how characters are compared) and case sensitivity. Use collation when you need language-aware sorting.
MySQL Example:
SELECT Name FROM Products ORDER BY Name COLLATE utf8_general_ci;
How this query works:
- SELECT Name: Displays the product names.
- FROM Products: Retrieves records from the Products table.
- ORDER BY Name: Sorts the product names alphabetically.
- COLLATE utf8_general_ci: Specifies a MySQL collation that performs case-insensitive comparisons. The CI means case-insensitive.
If you want case-insensitive sorting without changing collation, use
LOWER():
SELECT Name FROM Products ORDER BY LOWER(Name);
How this query works:
- SELECT Name: Displays the original product names.
- FROM Products: Retrieves product records from the Products table.
- LOWER(Name): Converts each product name to lowercase for sorting purposes.
- ORDER BY LOWER(Name): Sorts the results alphabetically using the lowercase versions of the names.
Important: LOWER() affects how the records are sorted, but it does not change the original Name values displayed in the results. Collation settings can also affect accent sensitivity and language-specific sorting rules.
ORDER BY with GROUP BY and Window Functions
ORDER BY is also commonly used after GROUP BY to sort the summarized results:
SELECT Category, SUM(Revenue) AS TotalRevenue FROM Sales GROUP BY Category ORDER BY TotalRevenue DESC;
How this query works:
- SELECT Category: Displays the name of each sales category.
- SUM(Revenue): Adds together the revenue values for each category.
- AS TotalRevenue: Names the calculated total TotalRevenue.
- FROM Sales: Retrieves sales records from the Sales table.
- GROUP BY Category: Groups sales records by category so SQL Server calculates a separate revenue total for each category.
- ORDER BY TotalRevenue DESC: Sorts the summarized results from the highest revenue total to the lowest.
Example: If the Electronics category generates $50,000, Clothing generates $30,000, and Furniture generates $20,000, Electronics appears first because it has the highest total revenue.
When using window functions like
ROW_NUMBER() you often combine PARTITION BY with an inner ORDER BY to rank rows:
SELECT OrderID, CustomerID, OrderDate,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
FROM Orders;
How this query works:
- SELECT OrderID, CustomerID, OrderDate: Displays each order's ID, customer ID, and order date.
- FROM Orders: Retrieves the order records from the Orders table.
- ROW_NUMBER(): Assigns a sequential number to each row based on the specified sorting rules.
- OVER(): Defines how SQL Server should organize and sort the rows when assigning row numbers.
- PARTITION BY CustomerID: Divides the orders into separate groups based on CustomerID. Each customer gets their own numbering sequence.
- ORDER BY OrderDate DESC: Within each customer group, places the newest order first.
- AS rn: Names the calculated row number column rn.
Example: If CustomerID 101 has three orders dated January 5, January 10, and January 15, the results assign row numbers as follows:
- January 15: rn = 1 (most recent order)
- January 10: rn = 2
- January 5: rn = 3 (oldest order)
Important: The ORDER BY inside ROW_NUMBER() determines how row numbers are assigned within each customer group. It does not guarantee the final display order of the query results. To sort the final output, add an outer ORDER BY clause.
▶ IWantToLearnSQL.com
FREE SQL VIDEO TUTORIAL
Watch: SQL ORDER BY Explained
Learn how to sort data using the ORDER BY clause with practical examples. Follow along as we explain the queries step by step.
▶ WATCH THE FREE VIDEOORDER BY and Performance Considerations
Sorting can be expensive on large datasets because the database often must create an in-memory or disk-based sort operation. Keep these tips in mind:
- • Limit rows when possible with
WHEREbefore sorting. - • Use indexed columns for ordering when suitable — some DB engines can avoid full sorts if an appropriate index exists.
- • Prefer
ORDER BYon simple columns rather than complex expressions when performance matters. - • Avoid ordering the entire dataset if you only need top N results — use
LIMIT/TOPwith an appropriate index.
-- Efficient: use index + LIMIT (MySQL/Postgres)
SELECT ProductID, Price FROM Products WHERE Category = 'Electronics' ORDER BY Price DESC LIMIT 10;
Common Mistakes & Gotchas
- • Assuming order without
ORDER BY. SQL results are unordered unless you explicitly specifyORDER BY. - • Sorting on non-deterministic expressions (e.g., functions returning random values).
- • Relying on implicit column positions in
ORDER BY(avoid using column numbers; use column names or aliases for clarity). - • Forgetting that
ORDER BYoccurs afterGROUP BYand aggregates.
Practical Exercises
Try these exercises:
- List the top 5 highest-priced products in each category (use
ORDER BYwithLIMIT). - Return users sorted by last login date, showing those who never logged in at the end (NULLS LAST/IS NULL trick).
- Sort orders by customer priority then by order date (multi-column sort).
Examples: Real-World Queries
Top-selling products in the last month
SELECT p.ProductName, SUM(ol.Quantity) AS SoldQty
FROM OrderLines ol
JOIN Products p ON p.ProductID = ol.ProductID
WHERE ol.OrderDate >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
GROUP BY p.ProductName
ORDER BY SoldQty DESC
LIMIT 10;
Sort customers by activity (active first)
SELECT CustomerID, LastActivity, IsActive FROM Customers ORDER BY IsActive DESC, LastActivity DESC;
When ORDER BY Changes Query Results
ORDER BY strictly changes only the result ordering; it doesn't change which rows are returned. However, when combined with
LIMIT or window functions, ordering affects which rows you pick.
Cross-Database Differences
Most SQL dialects implement
ORDER BY similarly, but details differ: NULLS FIRST/LAST (Postgres), index behavior (SQL Server), and collation syntax (MySQL). Test queries in your target DBMS.
FAQ — ORDER BY (Short Answers)
Do I always need ORDER BY to sort results?
Yes. SQL does not guarantee row order without
ORDER BY.
Can I use column positions in ORDER BY (e.g., ORDER BY 1)?
Technically yes in many DBs, but it reduces clarity and can break if you change the SELECT list. Prefer column names or aliases.
Does ORDER BY affect performance?
Yes — large sorts can be slow and use disk. Use indexes, LIMIT, and filters to reduce sorting costs.
How do I sort text in a language-aware way?
Use correct collation for your DB or apply
COLLATE where supported.
Conclusion
ORDER BY is a small clause with big power — it makes your results readable, report-ready, and reliable. Learn to combine multi-column sorting, null handling, and indexes to write efficient, predictable queries.