Sorting and limiting data

Sorting and limiting data

🔹 Sorting (ORDER BY)

You use ORDER BY to arrange query results:

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC;   -- ascending (default)

SELECT column1, column2
FROM table_name
ORDER BY column1 DESC;  -- descending

✅ You can sort by multiple columns:

SELECT name, age, city
FROM users
ORDER BY city ASC, age DESC;

🔹 Limiting (LIMIT)

You use LIMIT to restrict the number of rows returned.

SELECT column1, column2
FROM table_name
ORDER BY column1 DESC
LIMIT 10;

👉 Returns only the first 10 rows.

🔹 Pagination with LIMIT and OFFSET

For pages of results (common in apps):

SELECT column1, column2
FROM table_name
ORDER BY column1 DESC
LIMIT 10 OFFSET 20;

👉 Skips the first 20 rows, then returns 10.

🔹 SQL Variations

  • MySQL, PostgreSQL, SQLite → LIMIT works as above.
  • SQL Server → Uses TOP or OFFSET...FETCH:
SELECT TOP 10 column1, column2
FROM table_name
ORDER BY column1 DESC;

Or:

SELECT column1, column2
FROM table_name
ORDER BY column1 DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
  • Oracle → Uses FETCH:
SELECT column1, column2
FROM table_name
ORDER BY column1 DESC
FETCH FIRST 10 ROWS ONLY;

Leave a Reply