Skip to content
SQL

SQL SELECT Statement: Columns, Aliases, DISTINCT and LIMIT

Ihsan Ullah · Updated · 11 min read
SQL SELECT statement cover for Lesson 4 of the free SQL course: columns, aliases, DISTINCT and LIMIT

SQL course · Module 2: Querying one table · Lesson 4 of 40

Quick answer: The SQL SELECT statement reads rows from a table. SELECT * FROM products; returns every column, SELECT name, price FROM products; returns only the columns you list, AS gives a result column a new name, DISTINCT removes duplicate rows, and LIMIT 3 returns at most three rows. Always add ORDER BY when you use LIMIT, so you know which rows you get.

In this lesson you will learn:

  • how to select all columns or only the ones you need
  • how to calculate new columns and name them with AS
  • when a name needs double quotes and when a value needs single quotes
  • how DISTINCT and LIMIT work, and why LIMIT needs ORDER BY

Before you start: Lesson 3, Install PostgreSQL and Load a Sample Database for SQL Practice.

Open psql on the practice database with psql -U postgres -d shop and type each query. On Linux, Postgres.app or Homebrew, change the command as Lesson 3 explains. We ran every example on PostgreSQL 16.15. Everything used here is described the same way in the current PostgreSQL 18 manual, so it works the same on PostgreSQL 17 and 18.

Select all columns with SELECT *

SELECT * FROM products;
 id |            name             |  category   | price  
----+-----------------------------+-------------+--------
  1 | Wireless Mouse              | Electronics |  24.99
  2 | Mechanical Keyboard         | Electronics |  89.00
  3 | USB-C Hub                   | Electronics |  39.50
  4 | Laptop Stand                | Accessories |  45.00
  5 | Desk Lamp                   | Home Office |  32.00
  6 | Notebook Pack               | Stationery  |  12.50
  7 | Gel Pens 10-Pack            | Stationery  |   6.75
  8 | Noise-Cancelling Headphones | Electronics | 149.00
  9 | Ergonomic Chair             | Home Office | 259.00
 10 | HD Webcam                   | Electronics |  54.90
(10 rows)

This statement has two parts. SELECT says which columns you want, and FROM says which table to read. The star * means “all columns”, in the order the table defines them. The semicolon ends the statement. The last line, (10 rows), is psql telling you how many rows came back. It is not part of the data.

We did not ask for any order. The rows came back by id because the table was just loaded, but the PostgreSQL docs say that without sorting, rows “will be returned in an unspecified order” (PostgreSQL docs: Sorting Rows). From now on we add ORDER BY id to our queries, so your rows come out in the same order as ours. Lesson 7 explains ORDER BY in full.

SELECT * is handy for a first look at a table. In saved queries and programs, list the columns you need instead. The result is smaller, and the query does not change shape when someone adds a column to the table later.

Select only the columns you need

Write the column names after SELECT, separated by commas. These are the columns of the products table from Lesson 1.

SELECT name, price FROM products ORDER BY id;
            name             | price  
-----------------------------+--------
 Wireless Mouse              |  24.99
 Mechanical Keyboard         |  89.00
 USB-C Hub                   |  39.50
 Laptop Stand                |  45.00
 Desk Lamp                   |  32.00
 Notebook Pack               |  12.50
 Gel Pens 10-Pack            |   6.75
 Noise-Cancelling Headphones | 149.00
 Ergonomic Chair             | 259.00
 HD Webcam                   |  54.90
(10 rows)

The result has the columns in the order you write them, not the order of the table:

SELECT price, name, category FROM products ORDER BY id LIMIT 3;
 price |        name         |  category   
-------+---------------------+-------------
 24.99 | Wireless Mouse      | Electronics
 89.00 | Mechanical Keyboard | Electronics
 39.50 | USB-C Hub           | Electronics
(3 rows)

LIMIT 3 keeps the output short. It is explained at the end of this lesson.

Expressions: calculate new columns

The select list can hold calculations, not only column names. Here is every price with a 10% discount:

SELECT name, price, price * 0.9 FROM products ORDER BY id;
            name             | price  | ?column? 
-----------------------------+--------+----------
 Wireless Mouse              |  24.99 |   22.491
 Mechanical Keyboard         |  89.00 |   80.100
 USB-C Hub                   |  39.50 |   35.550
 Laptop Stand                |  45.00 |   40.500
 Desk Lamp                   |  32.00 |   28.800
 Notebook Pack               |  12.50 |   11.250
 Gel Pens 10-Pack            |   6.75 |    6.075
 Noise-Cancelling Headphones | 149.00 |  134.100
 Ergonomic Chair             | 259.00 |  233.100
 HD Webcam                   |  54.90 |   49.410
(10 rows)

Two things to notice. The new column has no real name, so PostgreSQL calls it ?column?. And the result has three decimal places, because a price with 2 decimals times 0.9 (1 decimal) keeps all 3. The table itself does not change: a SELECT only reads data.

Name columns with aliases (AS)

AS gives a result column a name. This name is called an alias.

SELECT name, price, round(price * 0.9, 2) AS sale_price FROM products ORDER BY id;
            name             | price  | sale_price 
-----------------------------+--------+------------
 Wireless Mouse              |  24.99 |      22.49
 Mechanical Keyboard         |  89.00 |      80.10
 USB-C Hub                   |  39.50 |      35.55
 Laptop Stand                |  45.00 |      40.50
 Desk Lamp                   |  32.00 |      28.80
 Notebook Pack               |  12.50 |      11.25
 Gel Pens 10-Pack            |   6.75 |       6.08
 Noise-Cancelling Headphones | 149.00 |     134.10
 Ergonomic Chair             | 259.00 |     233.10
 HD Webcam                   |  54.90 |      49.41
(10 rows)

round(value, 2) rounds to 2 decimal places. Lesson 8 covers functions like this. Notice that 6.075 became 6.08: for numeric values, PostgreSQL breaks ties “by rounding away from zero” (PostgreSQL docs: Mathematical Functions).

Expressions can use several columns. Each row in order_items has a quantity and the unit price paid, so the line total is their product:

SELECT order_id, product_id, quantity, unit_price,
       quantity * unit_price AS line_total
FROM order_items
ORDER BY order_id, product_id
LIMIT 5;
 order_id | product_id | quantity | unit_price | line_total 
----------+------------+----------+------------+------------
        1 |          1 |        1 |      22.99 |      22.99
        1 |          6 |        2 |      12.50 |      25.00
        2 |          1 |        1 |      22.99 |      22.99
        2 |          2 |        1 |      89.00 |      89.00
        3 |          8 |        1 |     149.00 |     149.00
(5 rows)

Order 1 bought 2 notebook packs at 12.50, so that line is 25.00. A query can be split over several lines like this; psql runs it when it reaches the semicolon.

PostgreSQL also lets you leave out FROM to calculate a single value:

SELECT 2 + 3 AS answer;
 answer 
--------
      5
(1 row)

The PostgreSQL docs note that “some other SQL databases cannot do this” without a dummy one-row table (PostgreSQL docs: SELECT).

AS is optional, but write it

SELECT name product, price AS "Price (USD)" FROM products ORDER BY id LIMIT 3;
       product       | Price (USD) 
---------------------+-------------
 Wireless Mouse      |       24.99
 Mechanical Keyboard |       89.00
 USB-C Hub           |       39.50
(3 rows)

name product works without AS. The docs say AS “is usually optional”, but when the alias is one of certain PostgreSQL key words (such as from or year) you must write AS or put the alias in double quotes, and they recommend that you “always either write AS or double-quote the output column name” (PostgreSQL docs: Select Lists). The second alias is in double quotes because it contains spaces, brackets and capital letters.

Upper case, lower case and double quotes

SELECT NAME, Price AS Sale FROM PRODUCTS ORDER BY id LIMIT 3;
        name         | sale  
---------------------+-------
 Wireless Mouse      | 24.99
 Mechanical Keyboard | 89.00
 USB-C Hub           | 39.50
(3 rows)

This works, and every heading is in lower case, even the alias Sale. In PostgreSQL, “key words and unquoted identifiers are case-insensitive”, and “unquoted names are always folded to lower case”. But “quoting an identifier also makes it case-sensitive” (PostgreSQL docs: Lexical Structure). So a quoted "Name" looks for a column with a capital N, which does not exist:

SELECT "Name" FROM products;
ERROR:  column "Name" does not exist
LINE 1: SELECT "Name" FROM products;
               ^
HINT:  Perhaps you meant to reference the column "products.name".

The HINT line even suggests the fix. Remember the two kinds of quotes:

  • Single quotes make a text value, such as 'Pakistan'.
  • Double quotes make a name (a table, column or alias), such as "Price (USD)".

The same docs note that folding to lower case is PostgreSQL behaviour; the SQL standard folds to upper case. The simplest habit: write table and column names in lower case without quotes, as the shop database does.

Remove duplicates with DISTINCT

Ten customers come from seven countries, so the country column has repeats:

SELECT country FROM customers ORDER BY country;
 country  
----------
 Canada
 France
 Germany
 Italy
 Pakistan
 Pakistan
 Pakistan
 Pakistan
 UK
 USA
(10 rows)

DISTINCT, written right after SELECT, removes duplicate rows: “one row is kept from each group of duplicates” (PostgreSQL docs: SELECT).

SELECT DISTINCT country FROM customers ORDER BY country;
 country  
----------
 Canada
 France
 Germany
 Italy
 Pakistan
 UK
 USA
(7 rows)

DISTINCT and a missing value

SELECT DISTINCT city FROM customers ORDER BY city;
  city   
---------
 Berlin
 Chicago
 Karachi
 Lahore
 London
 Milan
 Paris
 Toronto
 
(9 rows)

Two customers live in Lahore, so it appears once. The empty last row is Hira Siddiqui, who has no city: her city is NULL, and psql prints NULL as an empty cell. For DISTINCT, “null values are considered equal”, so all NULLs become one row (PostgreSQL docs: Select Lists). Lesson 6 is all about NULL.

DISTINCT looks at the whole row

SELECT DISTINCT country, city FROM customers ORDER BY country, city;
 country  |  city   
----------+---------
 Canada   | Toronto
 France   | Paris
 Germany  | Berlin
 Italy    | Milan
 Pakistan | Karachi
 Pakistan | Lahore
 Pakistan | 
 UK       | London
 USA      | Chicago
(9 rows)

With two columns, DISTINCT removes rows where both values repeat. Pakistan now appears three times, because it has three different cities (Karachi, Lahore and no city). Only the second Pakistan and Lahore row was removed.

Limit the number of rows with LIMIT

LIMIT n returns at most n rows. Combined with ORDER BY price DESC (highest price first), it gives the three most expensive products:

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;
            name             | price  
-----------------------------+--------
 Ergonomic Chair             | 259.00
 Noise-Cancelling Headphones | 149.00
 Mechanical Keyboard         |  89.00
(3 rows)

Without ORDER BY, LIMIT 3 just gives you any three rows. The docs warn that you then “will get an unpredictable subset of the query’s rows” (PostgreSQL docs: LIMIT and OFFSET).

The SQL standard has its own way to write this, and PostgreSQL accepts it too:

SELECT name, price FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY;
            name             | price  
-----------------------------+--------
 Ergonomic Chair             | 259.00
 Noise-Cancelling Headphones | 149.00
 Mechanical Keyboard         |  89.00
(3 rows)

Dialect note. The docs call LIMIT “PostgreSQL-specific syntax, also used by MySQL”, while FETCH FIRST comes from the SQL:2008 standard. SQLite also supports LIMIT (SQLite: SELECT). SQL Server uses TOP instead: SELECT TOP (3) name, price FROM products ORDER BY price DESC;. Microsoft’s docs say “always use an ORDER BY clause with the TOP clause” (Microsoft Learn: TOP). Lesson 2 compares these databases.

Try it yourself

Exercise 1: Product list with nice headings

Show the name and category of every product, in id order. The column headings must be Product and Category, with capital first letters.

Exercise 2: Which statuses exist?

List each order status from the orders table once, in alphabetical order.

Exercise 3: The newest orders

Show the id, date and status of the three most recent orders.

Solutions

Solution 1

SELECT name AS "Product", category AS "Category" FROM products ORDER BY id;
           Product           |  Category   
-----------------------------+-------------
 Wireless Mouse              | Electronics
 Mechanical Keyboard         | Electronics
 USB-C Hub                   | Electronics
 Laptop Stand                | Accessories
 Desk Lamp                   | Home Office
 Notebook Pack               | Stationery
 Gel Pens 10-Pack            | Stationery
 Noise-Cancelling Headphones | Electronics
 Ergonomic Chair             | Home Office
 HD Webcam                   | Electronics
(10 rows)

Without the double quotes, the headings would be folded to product and category.

Solution 2

SELECT DISTINCT status FROM orders ORDER BY status;
  status   
-----------
 cancelled
 delivered
 pending
 shipped
(4 rows)

The 22 orders use only four statuses.

Solution 3

SELECT id, order_date, status FROM orders ORDER BY order_date DESC LIMIT 3;
 id | order_date | status  
----+------------+---------
 22 | 2026-10-30 | pending
 21 | 2026-10-27 | shipped
 20 | 2026-10-21 | shipped
(3 rows)

ORDER BY order_date DESC puts the newest order first, and LIMIT 3 keeps three rows.

Common mistakes

  • Forgetting a comma. SELECT name price FROM products gives no error. PostgreSQL reads price as an alias for name, so you get one column called price that is full of product names.
  • A comma before FROM. SELECT name, price, FROM products; fails with ERROR: syntax error at or near "FROM". Remove the last comma.
  • Single quotes around a column name. SELECT 'name' FROM products returns the word name on every row, in a column called ?column?. Use no quotes, or double quotes.
  • Spaces in an alias without double quotes. SELECT price AS unit price FROM products; fails with ERROR: syntax error at or near "price". Write AS "unit price" or AS unit_price.
  • A wrong table name. SELECT name FROM product; fails with ERROR: relation "product" does not exist. The table is products; type \dt in psql to see the real names.

Frequently asked questions

Is SELECT * bad practice?

It is fine for exploring a table by hand. In code, reports and views, list the columns you need: the query returns less data, and its result does not change when someone adds or reorders columns in the table.

Is the AS keyword required in SQL?

Usually not, but write it anyway. PostgreSQL requires AS (or double quotes) when the alias is one of certain key words, such as year, and its docs recommend always writing AS or double-quoting the alias. It also makes a missing comma easier to spot.

Does SELECT DISTINCT apply to all columns?

Yes. DISTINCT compares whole result rows, so SELECT DISTINCT country, city removes a row only when both the country and the city repeat. To get one row per value of one column, select only that column.

Summary

  • SELECT columns FROM table; reads data; * means all columns, and the result follows the order of your list.
  • The select list can hold expressions like quantity * unit_price; name them with AS, or psql shows ?column?.
  • Unquoted names are folded to lower case; double quotes keep capitals and spaces; single quotes are for text values.
  • DISTINCT removes duplicate rows, compares all selected columns, and treats NULLs as equal.
  • LIMIT n (or FETCH FIRST n ROWS ONLY) returns at most n rows; use it with ORDER BY, and use TOP in SQL Server.

These few clauses are most of the daily work in the data analyst roadmap: almost every report starts with a SELECT.

Next lesson

Next: Lesson 5, SQL WHERE Clause: Filter Rows with AND, OR, IN, BETWEEN and LIKE.

Sources

Ihsan Ullah

Written & tested by Ihsan Ullah

Data analyst with 3 years of experience and a Bachelor's degree in Computer Science. I write clear, practical guides on data, SQL, Python and AI.

More about me →

Leave a comment

Your email address will not be published. Required fields are marked *