A database does not promise any particular order for the rows it returns. If you want the cheapest products first, or the newest orders at the top, you have to ask. SQL ORDER BY sorts the result, and LIMIT cuts it down to the number of rows you need. Together they power almost every “top 10” list and every page of search results on the web.
Sorting with SQL ORDER BY
ORDER BY goes after WHERE (if there is one) and names the column to sort by. Add ASC for ascending order (smallest first, A to Z, oldest date first) or DESC for descending order. ASC is the default, so most people leave it out.
SELECT ProductName, Price
FROM Products
ORDER BY Price DESC;Returns 15 rows, starting with Ponni Rice (420) and ending with Toned Milk (54).
Numbers sort by value, text sorts alphabetically, and our YYYY-MM-DD date strings sort by date. In SQLite, text sorting is case-sensitive by default (capital letters come before small letters). MySQL usually ignores case when sorting, thanks to its default collation.
Sorting by more than one column
When several rows share the same value in the first sort column, you can add a second column to break the tie. Each column gets its own ASC or DESC. The query below groups employees by department name (A to Z) and, inside each department, shows the highest salary first.
SELECT FirstName, Department, Salary
FROM Employees
ORDER BY Department ASC, Salary DESC;Returns 8 rows: Design first, then the three Development staff (Rahul 85000, Nisha 78000, Ajay 62000), then Management, Sales and Support.
You can also sort by a calculated value or by a column alias, for example ORDER BY Price * 1.05 or ORDER BY FullName when FullName is an alias you created in the SELECT list. You may even sort by a column you are not showing.
Where do NULL values go?
SQLite and MySQL both treat NULL as smaller than any other value, so empty values appear first in ascending order and last in descending order. SQLite (version 3.30 and later) and PostgreSQL let you choose with NULLS FIRST or NULLS LAST. MySQL does not support those keywords, so there people often sort by State IS NULL first as a trick.
SELECT CustomerName, State
FROM Customers
ORDER BY State NULLS LAST, CustomerName;Returns 12 rows from Delhi to Telangana; the two customers with no state (Indus Grocers, Lotus Organics) come last.
Taking the first rows with LIMIT
LIMIT n returns at most n rows. It is the last part of the query, after ORDER BY. On its own LIMIT just takes whatever rows come first, which is not useful. Combined with ORDER BY it gives you a reliable “top n” list.
SELECT ProductName, Price
FROM Products
ORDER BY Price DESC
LIMIT 3;Returns 3 rows: Ponni Rice (420), Cow Ghee (340) and Filter Coffee Powder (280).
Pages of results with OFFSET
OFFSET skips a number of rows before LIMIT starts counting. This is how websites show results page by page. With 5 rows per page, page 1 uses OFFSET 0, page 2 uses OFFSET 5, page 3 uses OFFSET 10. The formula is OFFSET = (page - 1) × rows per page.
SELECT CustomerName, City
FROM Customers
ORDER BY CustomerName
LIMIT 5 OFFSET 5;Returns 5 rows (customers 6 to 10 alphabetically): Fresh Basket, Ganga Provisions, Harbour Foods, Indus Grocers, Jasmine Mini Mart.
Tip: Always sort when you paginate, and include a unique column (such as an ID) at the end of the ORDER BY if the main sort column can have ties. Otherwise the same row might appear on two pages, or on none.
LIMIT in other databases
This is one of the places where SQL dialects differ the most:
| Database | Top 3 rows | Rows 6–10 |
|---|---|---|
| SQLite | LIMIT 3 | LIMIT 5 OFFSET 5 |
| MySQL | LIMIT 3 | LIMIT 5 OFFSET 5 or LIMIT 5, 5 |
| SQL Server | SELECT TOP 3 … | OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY |
| Standard SQL | FETCH FIRST 3 ROWS ONLY | OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY |
In the short form LIMIT 5, 5, the first number is the offset and the second is the count – easy to mix up, so the longer LIMIT … OFFSET … form is clearer. SQLite understands both forms. SQLite does not support TOP, so it will not run in our editor.
Watch out: Large offsets get slow on big tables, because the database still has to read and throw away all the skipped rows. For very long lists, sites often use “keyset” paging instead (for example WHERE OrderID > last_seen_id ORDER BY OrderID LIMIT 20). For learning and small sites, OFFSET is fine.
Clause order so far
The clauses must appear in this order: SELECT → FROM → WHERE → ORDER BY → LIMIT. You will add GROUP BY and HAVING between WHERE and ORDER BY later. Before that, the next lesson, SQL INSERT, UPDATE and DELETE, shows how to change the data. If you skipped it, SQL WHERE covers the filtering used here.