Topic: PostgreSQL

A SELECT Needs Four Decisions: Columns, Rows, Order, Limit

A reliable SQL query does more than retrieve data: name the columns, filter with WHERE, define an order with ORDER BY, then cap the result with LIMIT.

Many “latest articles,” “recent orders,” or “my tasks” features eventually become a SELECT. The shortest version might be:

SELECT * FROM articles LIMIT 10;

It runs, but it has not finished describing the product requirement. Which data is needed? Which data is allowed to appear? What does latest mean? When two records have the same time, which comes first? If those answers are not in the query, a page looking correct today does not mean it will return the same data next time.

For a read from one table, I now make four decisions first: which columns to return, which rows to keep, how to order them, and how many to return. This is not about memorizing syntax order. It is about translating a requirement into the responsibility of each clause.

Start with one complete, still-small query

Suppose articles has id, title, status, and published_at. The home page needs the ten most recently published articles:

SELECT id, title, published_at
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC, id DESC
LIMIT 10;

Each line answers a different question:

When reading a query, separating those responsibilities is more useful than memorizing the database’s internal execution sequence. The same questions continue to work later with JOIN, GROUP BY, and subqueries.

SELECT: treat the output as a contract

The SELECT list is the shape of returned data. When a screen needs only a title and timestamp, writing title, published_at is more honest than SELECT *: the dependency is visible to the caller.

SELECT * is not an error. It is handy for inspecting a table in psql. But when application code keeps it, a new internal column can change the returned data without changing the query. PostgreSQL’s tutorial likewise describes it as convenient shorthand for ad-hoc queries that deserves care in production code.

This is not just a preference for sending fewer bytes. It makes an API or UI’s needs explicit. With named columns, review can ask a simpler question too: does this screen really need an email address or an internal note?

WHERE: decide which rows qualify

WHERE takes a condition. Only rows for which the condition is true remain in the result:

SELECT id, title
FROM articles
WHERE status = 'published';

That is different from fetching every row and filtering it in JavaScript. Placing the condition in the query asks the database to deliver only the needed result, and it keeps the reading rule in a place that can be inspected and tested.

When real user input becomes part of a condition, use a driver’s or ORM’s parameter binding rather than concatenating SQL strings. This article is about read responsibilities, not turning string assembly into another API.

ORDER BY: a table is not a queue

When a requirement says “latest 10,” ordering is not optional decoration. ORDER BY published_at DESC puts later times first. If no direction is written, the default is ASC, so earlier values come first.

The easier detail to miss is the tie-breaker. If two articles have the same timestamp, sorting only by published_at does not fully decide which one appears first. Add a stable second field:

ORDER BY published_at DESC, id DESC

This does not claim that id has the same business meaning as time. It makes the rule for equal times explicit. PostgreSQL’s documentation makes the same point: rows equal on a sort value can still be returned in a different order.

LIMIT: cut a result; do not invent an order

LIMIT 10 means return at most ten rows. If only three rows match, it returns three. It does not promise exactly ten rows, and it does not attach a meaning of “latest” to the data.

So this query is fine for a quick look, but it should not power a product’s latest-items list:

SELECT title
FROM articles
LIMIT 10;

Without ORDER BY, SQL makes no promise about row order. With LIMIT, even the chosen subset can change across executions. PostgreSQL is explicit: reliably slicing a result requires an ordering that first makes the outcome predictable.

That is why ORDER BY followed by LIMIT is not merely neat formatting. It is the data contract for pages, feeds, and recent-item features.

A small process for turning a requirement into a query

For a read requirement, I first write four sentences in plain language:

1. Which fields should this screen or API show?
2. Which rows qualify?
3. What order does someone expect, and what happens on a tie?
4. How many rows are needed this time?

Then I place the answers into SELECT, WHERE, ORDER BY, and LIMIT. “Ten newest published articles” naturally becomes the five-line SQL above. “All published articles sorted by title” does not need a forced LIMIT.

The process also avoids two common mistakes: treating LIMIT as sorting and treating SELECT * as a permanent API contract. Give each part of the requirement a home first, and the query is easier to change, review, and explain when data grows.

What I learned

• I can split a single-table read into four decisions—columns, rows, order, and limit—instead of writing a SELECT that merely happens to run.
• ORDER BY defaults to ascending order. When newer data must appear first, I need to write DESC explicitly.
• LIMIT only caps quantity; it cannot make an unordered result stable, and ties need an explicit tie-breaker.
• SELECT * is useful for exploration, while application code should name the fields it truly needs and make the output reviewable.

Conclusion: define the result before retrieving it

Learning SQL is not about memorizing every clause. It is about knowing which missing part of a requirement each clause supplies. A dependable read query explicitly states output, filter, order, and count.

Once those four decisions are clear, SELECT is no longer just “data came back.” It becomes a readable, testable, predictable read contract.

External references

• PostgreSQL: Querying a Table
• PostgreSQL: SELECT
• PostgreSQL: LIMIT and OFFSET
• daily.dev: Day 36 of #100DaysOfCode — SQL Basics
• Reddit: Beginner discussion of SELECT, WHERE, LIKE, and LIMIT
• Reddit: Beginner discussion of ORDER BY, LIMIT, and OFFSET

Further learning

• CS50 SQL Week 0: Querying is Harvard CS50’s official video lesson for practicing SELECT, WHERE, ORDER BY, and LIMIT.