Learn SQL with PostgreSQL - Filtering, Sorting, Paging & Aggregation Queries

#Introduction

In this episode, we'll cover four core abilities: reading and filtering data with SELECT and WHERE, sorting with ORDER BY, paging with LIMIT and OFFSET, and aggregating data with aggregate functions, GROUP BY, and HAVING.

#Reading & Filtering Data

#Basic SELECT and Column Aliases

The most fundamental command: SELECT reads columns from a table. Use the asterisk * for all columns, or name specific columns.

SELECT * FROM users;
SELECT email, full_name FROM users;

Aliases with AS make the output easier to read, especially for computed columns or columns with long names:

SELECT
    email AS alamat_email,
    age * 2 AS usia_dobel
FROM users;

#DISTINCT to Remove Duplicates

SELECT DISTINCT category FROM products;
SELECT DISTINCT country FROM customers ORDER BY country;

#Comparison Operators in WHERE

WHERE is the filtering clause. These values are your main filtering "weapons":

SELECT email, age
FROM users
WHERE age BETWEEN 20 AND 30
  AND is_active = TRUE;

#LIKE vs ILIKE

The key difference for text search in PostgreSQL: LIKE is case-sensitive, while ILIKE ignores case. Its wildcard characters: % matches zero or more characters, _ matches exactly one character.

SELECT email FROM users WHERE email LIKE 'budi%';
SELECT name FROM products WHERE name ILIKE '%sepatu%';

The second query with ILIKE will find "Sepatu Lari", "sepatu futsal", and "SEPATU BOLA" — matching regardless of capitalization.

#Logical Operators: AND, OR, NOT

Combine multiple conditions with logical operators:

• AND: all conditions must be true.
• OR: at least one condition must be true.
• NOT: negates a condition's result.

SELECT email FROM users
WHERE is_active = TRUE
  AND (age >= 30 OR country = 'ID')
  AND NOT email ILIKE '%@temp.com';

Notice the parentheses in the example above — without them, the OR operator would be interpreted with a precedence that may differ from what you intended. Parentheses are the explicit way to state intent.

#Sorting & Limiting Data

#ORDER BY with NULLS FIRST/LAST

ORDER BY sorts the query results. The default is ASC (smallest to largest), and NULL is considered larger than all values, so it appears last with ASC. With NULLS FIRST / NULLS LAST, you can control its position explicitly.

SELECT email, last_login
FROM users
ORDER BY last_login DESC NULLS LAST;

#Pagination: LIMIT, OFFSET, and FETCH FIRST

LIMIT and OFFSET:

SELECT email FROM users ORDER BY id LIMIT 20 OFFSET 0;
SELECT email FROM users ORDER BY id LIMIT 20 OFFSET 20;

For ANSI SQL standard compliance, PostgreSQL also supports FETCH FIRST n ROWS ONLY:

SELECT name, price FROM products
ORDER BY price DESC
FETCH FIRST 10 ROWS ONLY;

#Data Aggregation & Grouping

Aggregation is the ability to turn many rows into a single summary. It's the foundation of all reports and dashboards.

#Aggregate Functions

SELECT
    COUNT(*) AS total_users,
    AVG(age) AS rata_usia,
    MIN(age) AS termuda,
    MAX(age) AS tertua
FROM users;

#GROUP BY and HAVING

GROUP BY groups rows by column values, then aggregation is calculated per group:

SELECT country, COUNT(*) AS jumlah_user
FROM users
WHERE is_active = TRUE
GROUP BY country
HAVING COUNT(*) >= 5
ORDER BY jumlah_user DESC;

WHERE filters rows before grouping, HAVING filters groups after aggregation.

#Distribution with COUNT and GROUP BY

SELECT
    DATE_TRUNC('month', created_at) AS bulan,
    COUNT(*) AS jumlah_order,
    SUM(total) AS total_revenue
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY bulan
ORDER BY bulan;

DATE_TRUNC('month', ...) truncates the timestamp to the start of the month, so all orders in the same month end up in one group. The result is a sales report per month in seconds.

#Closing

Key takeaways:

• WHERE filters rows; HAVING filters the groups produced by aggregation.
• ILIKE is PostgreSQL's case-insensitive text search.
• ORDER BY has NULLS FIRST/LAST control that's often forgotten.
• FETCH FIRST n ROWS ONLY is the ANSI standard alternative to LIMIT.
• GROUP BY + DATE_TRUNC is the go-to combination for time-based reports.

In episode 6, we'll dive into one of the most important concepts of relational databases: Advanced Table Joins (Relationships Between Tables) — from INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN, to SELF JOIN for hierarchical data. This is the moment where relational databases show their true power.