Mastering PostgreSQL SELECT: Filter, Sort & Limit

Filtering, Sorting, and Limiting Results

Start the PostgreSQL environment by clicking the Start PostgreSQL button. Wait for it to finish setting up.

A sample web application is already connected to a real PostgreSQL database. Click the link to open it. The application shows products with filtering, ordering, and pagination controls.

The buttons filter by category using a WHERE clause. Clicking Starters returns only starter dishes. Clicking All returns everything.

The sort controls use an ORDER BY clause. Selecting Price with Ascending shows the cheapest dish first. Selecting Descending shows the most expensive first.

The pagination buttons at the bottom load the next set of products. Clicking page 2 skips the first six products and shows the next six.

Connect to the Database

Open VS Code, then open the integrated terminal from the menu (three lines → Terminal → New Terminal). Maximize the terminal window.

Verify PostgreSQL is running:

sudo systemctl status postgresql

Connect to the PostgreSQL command line:

sudo -u postgres psql

List the databases:

\l

Connect to the restaurant database:

\c restaurant

View the menu table:

SELECT * FROM menu;

You should see 13 dishes. Clear the terminal:

\!clear

The WHERE Clause

The WHERE clause filters rows based on conditions.

Get all items in the main category:

SELECT * FROM menu WHERE category = 'main';

Get items with a price less than 10:

SELECT * FROM menu WHERE price < 10;

Chain multiple conditions with AND:

SELECT * FROM menu WHERE price < 10 AND category = 'desserts';

Both conditions must be true for a row to be returned.

The ORDER BY Clause

ORDER BY sorts the results by a specified column.

Sort by price ascending (cheapest first):

SELECT * FROM menu ORDER BY price ASC;

Sort by price descending (most expensive first):

SELECT * FROM menu ORDER BY price DESC;

The LIMIT Clause

LIMIT caps the number of rows returned.

Get the three cheapest items:

SELECT * FROM menu ORDER BY price ASC LIMIT 3;

Combine all three clauses to get the most expensive dessert:

SELECT * FROM menu WHERE category = 'desserts' ORDER BY price DESC LIMIT 1;

The WHERE clause filters for desserts, ORDER BY sorts them by price descending, and LIMIT 1 returns only the most expensive one.

Pagination with LIMIT and OFFSET

Open the file /postgres/pagination-app/server/index.js in the workspace. The application uses these SQL patterns:

• SELECT COUNT(*) FROM menu WHERE category = $1 counts items in a category.
• SELECT id, name, price, category FROM menu WHERE category = $1 ORDER BY $2 $3 LIMIT $4 OFFSET $5 fetches a page of results.

LIMIT sets how many rows to return (six in this app). OFFSET skips a number of rows before starting to return results. For example, OFFSET 5 LIMIT 6 skips the first five rows and returns the next six.

The $1, $2 etc. are parameterized placeholders that prevent SQL injection. The actual values are passed separately.

Summary

• WHERE filters rows based on conditions.
• ORDER BY sorts results by a column, with ASC for ascending and DESC for descending.
• LIMIT caps the number of rows returned.
• OFFSET skips a specified number of rows before returning results, enabling pagination.