PostgreSQL SELECT Query Basics

1. What You'll Learn

• Use SELECT to query columns, expressions, and aliases
• Remove duplicate rows with DISTINCT
• Filter data with WHERE
• Sort with ORDER BY (including PostgreSQL's unique NULLS FIRST/LAST)
• Paginate with LIMIT/OFFSET and FETCH FIRST

2. The Story

Bob is a database engineer at an e-commerce platform. The platform generates about 10,000 orders per day, accumulating 100,000 records over the past 30 days. The operations team asks him to:

1. Query all orders from the last 30 days, sorted by amount from high to low
2. Show 50 per page, with paging support
3. Filter out cancelled orders
4. Drop duplicate customer IDs from the display

Bob needs SELECT, WHERE, ORDER BY, LIMIT/OFFSET, and DISTINCT to get the job done.

3. Concept: SELECT Basics

(1) SELECT Syntax Overview

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column [ASC|DESC] [NULLS FIRST|NULLS LAST]]
[LIMIT count [OFFSET start]];

SELECT is the most commonly used SQL statement, used to retrieve data from one or more tables.

(2) All Columns vs Specific Columns

▶ Example: Query All Columns

SELECT * FROM orders;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Query Specific Columns

SELECT order_id, customer_id, total_amount, order_date
FROM orders;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) Column Aliases

Use the AS keyword (optional) to give a column or expression an alias for more readable results.

▶ Example: Using Column Aliases

SELECT
  order_id,
  total_amount AS amount,
  order_date AS "Order Date",
  total_amount * 0.08 AS tax
FROM orders;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(4) Expressions and Computed Columns

SELECT can do more than read columns—it can also write arithmetic expressions, function calls, etc., to produce computed columns.

▶ Example: Computed Column

SELECT
  product_name,
  unit_price,
  unit_price * 1.1 AS price_with_tax,
  ROUND(unit_price * 1.1, 2) AS rounded_price
FROM products;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

4. Concept: DISTINCT De-duplication

(1) Basic De-duplication

DISTINCT removes duplicate rows from the result set, commonly used to count unique values.

▶ Example: Query Distinct Customer IDs

SELECT DISTINCT customer_id
FROM orders;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Multi-column De-duplication

DISTINCT applies to the combination of all specified columns, not a single column.

▶ Example: Multi-column Combination De-dup

SELECT DISTINCT customer_id, order_status
FROM orders;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) DISTINCT ON: PostgreSQL-only Syntax

DISTINCT ON (expr) de-duplicates by a given expression and returns the first row in each group (pair with ORDER BY to decide which row).

▶ Example: Each Customer's Latest Order

SELECT DISTINCT ON (customer_id)
  customer_id, order_id, order_date, total_amount
FROM orders
ORDER BY customer_id, order_date DESC;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

5. Concept: WHERE Filtering

(1) Basic Comparison Operators

▶ Example: Filter Completed Orders

SELECT order_id, customer_id, total_amount
FROM orders
WHERE order_status = 'completed';

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Date Filtering

Date comparison is one of the most common filtering needs in e-commerce.

▶ Example: Orders in the Last 30 Days

SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) Combining Multiple Conditions

Use AND, OR, NOT to combine conditions. Precedence: NOT > AND > OR; use parentheses to control explicitly.

▶ Example: Combined Condition Filter

SELECT order_id, total_amount, order_status
FROM orders
WHERE order_status = 'completed'
  AND total_amount > 500
  AND order_date >= '2025-01-01';

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

6. Concept: ORDER BY Sorting

(1) Basic Sorting

ORDER BY defaults to ascending (ASC); you can specify descending (DESC). Supports sorting by column name, alias, or column position.

▶ Example: Sort by Amount Descending

SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY total_amount DESC;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Multi-column Sorting

With multi-column sorting, rows are sorted by the first column; ties are broken by the second column, and so on.

▶ Example: Sort by Status, then by Amount

SELECT order_id, order_status, total_amount
FROM orders
ORDER BY order_status ASC, total_amount DESC;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) NULLS FIRST / NULLS LAST: PostgreSQL Feature

PostgreSQL treats NULL as the largest value by default (last in ascending, first in descending). You can explicitly control NULL placement with NULLS FIRST and NULLS LAST.

▶ Example: NULLS FIRST/LAST Control

SELECT product_name, discount_rate
FROM products
ORDER BY discount_rate DESC NULLS LAST;

Output:

 count 
-------
     5
(1 row)

7. Concept: LIMIT and FETCH FIRST Pagination

(1) LIMIT / OFFSET

LIMIT caps the number of returned rows; OFFSET skips a given number of rows. Together they implement pagination.

▶ Example: 50 per page, page 3

SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE order_status = 'completed'
ORDER BY total_amount DESC
LIMIT 50 OFFSET 100;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) FETCH FIRST: SQL Standard Syntax

PostgreSQL also supports the SQL-standard FETCH FIRST syntax, semantically identical to LIMIT.

▶ Example: FETCH FIRST Pagination

SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY total_amount DESC
OFFSET 100 ROWS
FETCH FIRST 50 ROWS ONLY;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) Pagination Formula

▶ Example: Dynamic Pagination (pseudo-code)

page=3
size=50
offset=$(( (page - 1) * size ))
psql -c "SELECT order_id, total_amount FROM orders ORDER BY total_amount DESC LIMIT $size OFFSET $offset;"

Output:

# psql command executed successfully

8. Flowchart: SELECT Query Execution Order

flowchart TD
    A[FROM orders] --> B[WHERE filter]
    B --> C[SELECT columns / expressions]
    C --> D[DISTINCT]
    D --> E[ORDER BY sort]
    E --> F[LIMIT / OFFSET paginate]
    F --> G[Result Set]

    style A fill:#e1f5fe
    style B fill:#fff3e0
    style C fill:#e8f5e9
    style D fill:#f3e5f5
    style E fill:#fce4ec
    style F fill:#e0f2f1
    style G fill:#c8e6c9

flowchart TD
    A[FROM orders] --> B[WHERE filter]
    B --> C[SELECT columns / expressions]
    C --> D[DISTINCT]
    D --> E[ORDER BY sort]
    E --> F[LIMIT / OFFSET paginate]
    F --> G[Result Set]

    style A fill:#e1f5fe
    style B fill:#fff3e0
    style C fill:#e8f5e9
    style D fill:#f3e5f5
    style E fill:#fce4ec
    style F fill:#e0f2f1
    style G fill:#c8e6c9

9. Comprehensive Example

Bob completed the operations team's requirements: query completed orders from the last 30 days, paginate by amount descending, and report distinct customer counts.

-- Step 1: Recent 30-day completed orders, paginated by amount DESC
SELECT
  order_id,
  customer_id,
  total_amount,
  total_amount * 0.08 AS tax_amount,
  order_date AS "Order Date",
  order_status
FROM orders
WHERE order_status = 'completed'
  AND order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY total_amount DESC NULLS LAST
LIMIT 50 OFFSET 0;

-- Step 2: Count distinct customers in those orders
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_status = 'completed'
  AND order_date >= CURRENT_DATE - INTERVAL '30 days';

-- Step 3: Each customer's latest order (DISTINCT ON)
SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  total_amount,
  order_date
FROM orders
WHERE order_status = 'completed'
  AND order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY customer_id, total_amount DESC;

-- Step 4: Top 10 orders with computed total (price + tax)
SELECT
  order_id,
  customer_id,
  total_amount,
  ROUND(total_amount * 1.08, 2) AS total_with_tax,
  order_date
FROM orders
WHERE order_status = 'completed'
  AND order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY total_with_tax DESC
FETCH FIRST 10 ROWS ONLY;

❓ FAQ

📖 Summary

• SELECT is the foundation of SQL queries, supporting column names, expressions, and aliases
• DISTINCT removes duplicate rows; DISTINCT ON is PostgreSQL-specific syntax
• WHERE filters rows with comparison operators and supports AND/OR/NOT combinations
• ORDER BY supports ASC/DESC and PostgreSQL's unique NULLS FIRST/LAST
• LIMIT/OFFSET and FETCH FIRST implement pagination; large offsets call for keyset pagination
• SQL logical execution order: FROM → WHERE → SELECT → DISTINCT → ORDER BY → LIMIT

📝 Exercises

1. ⭐ Write a query selecting product_name and unit_price from products, sorted ascending by price, returning only the first 10 rows.

2. ⭐⭐ Write a query selecting orders from the last 7 days from orders, filtering for total_amount > 200, sorted by order_date DESC, using FETCH FIRST 20 ROWS ONLY.

3. ⭐⭐⭐ Use DISTINCT ON to query each customer_id's highest-amount order (hint: ORDER BY customer_id, total_amount DESC), and count how many of those customers have a max single order over 1,000 USD.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us