5. ORDER BY, DISTINCT and Top-N Results
5.5 OFFSET for Simple Pagination (Idea Only)
Why. Apps show page 2 of results — OFFSET is the idea behind that.
What. OFFSET skips rows after sorting. LIMIT 5 OFFSET 5 ≈ page 2 of size 5.
How. ORDER BY … LIMIT 5 OFFSET 5;
Steps
- Get top 5.
- Get next 5 with OFFSET 5.
- Compare the two pages.
Examples
Example 1 · dataset: raja_coffee
SELECT order_id, amount_inr
FROM orders
ORDER BY amount_inr DESC
LIMIT 5 OFFSET 5;Output
| order_id | amount_inr |
|---|---|
| 1019 | 900.0 |
| 1003 | 890.0 |
| 1006 | 890.0 |
| 1012 | 890.0 |
| 1022 | 890.0 |
Meaning. Rows 6–10 by amount — second page idea.
Ravindra Bagale's Tip
Deep OFFSET on huge tables can be slow later — idea only for now.
Ravindra Bagale's Tip – मराठी
खूप मोठ्या टेबलवर deep OFFSET मंद असू शकतो — आत्ता फक्त कल्पना.
Ravindra Bagale's Tip – हिंदी
बहुत बड़ी टेबल पर deep OFFSET धीमा हो सकता है — अभी सिर्फ आइडिया।