MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
When querying large enterprise tables, sorting by a single column often produces ties: multiple employees work in the same department, or multiple products share the exact same price. Multi-Column Sorting allows you to specify a primary sort column followed by secondary and tertiary "tie-breaker" columns, each with its own independent sort direction.
Separate each column and its desired direction with a comma:
Sort employees by Department alphabetically, and within each department, sort by Salary descending (highest paid first):
+-------------+---------------+-------------+----------+ | employee_id | full_name | department | salary | +-------------+---------------+-------------+----------+ | 104 | Vikram Rao | Engineering | 95000.00 | <-- Highest salary in Engineering | 101 | Aarav Sharma | Engineering | 80000.00 | | 108 | Neha Gupta | Engineering | 65000.00 | | 102 | Priya Patel | Marketing | 72000.00 | <-- Highest salary in Marketing | 105 | Rohan Verma | Marketing | 54000.00 | +-------------+---------------+-------------+----------+
You can sort results by mathematical calculations, string manipulations, or date functions directly:
FIELD()What if you need to sort records in a specific, non-alphabetical business order? For example, sorting support tickets by priority: Critical -> High -> Medium -> Low.
Standard ASC or DESC will sort alphabetically (Critical -> High -> Low -> Medium), which is incorrect! MySQL provides the FIELD() function for custom ordering:
FIELD() Works:FIELD(str, str1, str2, ...) returns the 1-based index position of str within the list.'Critical' returns 1, 'High' returns 2, 'Medium' returns 3, 'Low' returns 4.ORDER BY clause sorts the rows by these returned numeric values (1, 2, 3, 4)!When you frequently sort by multiple columns (e.g., ORDER BY department ASC, salary DESC), MySQL can satisfy the query instantaneously with zero filesort overhead if a matching Composite Index is present:
sort_buffer_size). Stick to 1 to 3 primary sorting criteria.DESC at the end applies only to the column immediately preceding it! Writing ORDER BY col1, col2 DESC sorts col1 in ASC order and col2 in DESC order.ORDER BY state ASC, city DESC, how are the rows ordered?SELECT name, dept, salary FROM staff ORDER BY dept, salary DESC;, what is the sort direction of the dept column?ORDER BY category ASC, price DESC?ORDER BY clause sort records based on a mathematical expression involving multiple columns?Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
| Previous Lesson | Next Lesson |
|---|---|
| Sorting Data with ORDER BY | Restricting Rows with LIMIT |
Test your understanding of this lesson with 5 questions. Each question has one correct answer.