Why should you care?

E-commerce search on PostgresVerse shows cheapest products first — ORDER BY price ASC — or newest reviews — ORDER BY created_at DESC.

See it live — copy this example

Run in pgAdmin or psql.

SELECT product_id, name, price, rating
FROM products
WHERE in_stock = true
ORDER BY rating DESC NULLS LAST, price ASC
LIMIT 20;

What happened?

• Primary sort is highest rating.
• Ties break by lower price.
• NULLS LAST keeps unrated items at the bottom.
• LIMIT returns top 20 after sorting.

Practice next

1. Add in_stock boolean and rating numeric columns to products.
2. Insert rows with mixed ratings including NULL.
3. Run ORDER BY with and without NULLS LAST.
4. ORDER BY random() LIMIT 5 for a “deals spotlight” prototype.
5. Sort by expression: ORDER BY length(name) DESC for longest names.

Remember

ORDER BY is the last major sort step before LIMIT. Multi-column sorts use left-to-right tie-breaking. NULLS FIRST/LAST avoids surprise null placement.

PostgresVerse search ranking

App shows top-rated in-stock products under ₹500 using this sort pattern.

Outcome: Conversion improves because unrated junk sinks to page two.