Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 39 ยท T-SQL Querying

SELECT, TOP and DISTINCT in T-SQL

Write T-SQL SELECT queries: choose columns, use aliases, limit rows with TOP and TOP WITH TIES, remove duplicates with DISTINCT and understand logical processing order.

SELECT is the statement you will write more than any other. It reads rows from one or more tables and returns them as a result set. This lesson covers the T-SQL flavor: choosing columns, naming them with aliases, limiting the number of rows with TOP, removing duplicates with DISTINCT, and the order in which SQL Server logically evaluates each clause, which explains many puzzling errors.

Sample data: a small shop with categories, products, customers, orders and order_items. The runnable editors use that database. Where T-SQL syntax differs from the editor's dialect, the T-SQL is shown in a code block first.

The basic shape

SQL
SELECT column_list
FROM   source
WHERE  row_filter
GROUP BY ...
HAVING group_filter
ORDER BY sort_columns;

Only SELECT is required for a query that reads constants (SELECT 1 + 1;). Everything else is optional. Start with columns:

SQL Choose columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SELECT * returns every column. It is handy for exploration but a bad habit in application code and views: extra columns waste network and memory, and a later table change silently changes your result. List the columns you need.

Expressions and aliases

A select list can contain calculations. Give a calculated column a name with AS, which is optional but recommended:

SQL
SELECT name,
       price,
       price * 1.2 AS price_with_vat,
       stock * price AS stock_value
FROM dbo.products;

T-SQL also accepts the older alias = expression form, price_with_vat = price * 1.2, and many SQL Server scripts use it. Both are equivalent; pick one style. If an alias has spaces, wrap it in brackets: AS [Price With VAT].

SQL Calculated columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Notice that the query orders by the alias stock_value. That works in ORDER BY but not in WHERE, for reasons explained below.

Limiting rows with TOP

T-SQL's way to return only a few rows is TOP:

SQL
SELECT TOP (5) name, price
FROM dbo.products
ORDER BY price DESC;          -- the 5 most expensive products

Rules worth remembering:

  • Always pair TOP with ORDER BY. Without it, SQL Server returns an arbitrary set of rows that may change between runs.
  • The parentheses are optional for a literal (TOP 5) but required for a variable or expression: TOP (@n).
  • TOP (10) PERCENT returns a percentage of the rows, rounded up.
  • TOP (n) WITH TIES also returns every row that ties with the last row on the ORDER BY columns, so you may get more than n rows.
SQL
SELECT TOP (3) WITH TIES name, price
FROM dbo.products
ORDER BY price DESC;          -- if rows 3 and 4 have the same price, both appear

For paging through results (rows 21 to 40, then 41 to 60), use OFFSET ... FETCH, covered in Sorting and paging.

Other systems use LIMIT, so the editor below uses it as a labelled stand-in for TOP (5):

SQL Stand-in: TOP (5) with LIMIT ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Removing duplicates with DISTINCT

DISTINCT returns each different combination of the selected columns once:

SQL DISTINCT ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

With several columns, the combination must be unique: SELECT DISTINCT city, country returns each city-country pair once. DISTINCT has a cost, because SQL Server must sort or hash all rows to find duplicates. Do not sprinkle it on a query to hide duplicates caused by a wrong join; fix the join. COUNT(DISTINCT country) counts unique values:

SQL
SELECT COUNT(DISTINCT country) AS countries FROM dbo.customers;

Literals, NULL and SELECT without FROM

A select list can mix columns with constants. SELECT 'T-SQL' AS language, 2022 AS version; needs no FROM. A NULL literal has no type of its own, so SELECT NULL returns an INT column. SELECT ... INTO newtable creates a new table from a query result; later chapters use it for copies and temporary tables.

Logical query processing order

You write SELECT first, but SQL Server evaluates the clauses in a different logical order:

flowchart LR
  A["1 FROM / JOIN"] --> B["2 WHERE"]
  B --> C["3 GROUP BY"]
  C --> D["4 HAVING"]
  D --> E["5 SELECT"]
  E --> F["6 DISTINCT"]
  F --> G["7 ORDER BY"]
  G --> H["8 TOP / OFFSET"]

The logical order of evaluation. The optimizer may physically do things differently, but the result must match this order.

This explains common errors:

  • You cannot use a SELECT alias in WHERE, because WHERE (step 2) runs before the alias exists (step 5). Repeat the expression or use a subquery or CTE.
  • You can use the alias in ORDER BY (step 7), because it runs after SELECT.
  • Aggregates cannot appear in WHERE; they belong in HAVING, which runs after grouping.

The stand-in shows how to filter on a calculated value correctly:

SQL Filtering on a calculated value ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Qualified names and table aliases

When two tables have a column with the same name, qualify it with the table name or an alias: p.name. Table aliases are usually short (p, o, c) and are set in FROM: FROM dbo.products AS p. Once you alias a table, use the alias everywhere in that query, not the original name.

Common mistakes

  • SELECT * in production code. Name the columns.
  • TOP without ORDER BY. The result is not deterministic.
  • Expecting DISTINCT to apply to one column in SELECT DISTINCT a, b. It applies to the whole row.
  • Using an alias in WHERE. Repeat the expression or wrap the query.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which T-SQL clause limits a query to the first 10 rows?

    T-SQL uses TOP. LIMIT is MySQL, PostgreSQL and SQLite; ROWNUM is Oracle.
  2. 2. What does TOP (3) WITH TIES do?

    WITH TIES requires ORDER BY and may return more than 3 rows when later rows have the same sort values as the third.
  3. 3. Why does WHERE total > 100 fail when total is an alias defined in SELECT?

    Logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. WHERE cannot see SELECT aliases.
  4. 4. What does SELECT DISTINCT city, country return?

    DISTINCT applies to the entire row of selected columns, not to the first column only.

Summary

  • List the columns you need; use AS for aliases and brackets for unusual names.
  • TOP (n) limits rows in T-SQL; always add ORDER BY, and use WITH TIES to include ties.
  • DISTINCT removes duplicate rows but costs a sort or hash.
  • Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, TOP.
  • Aliases are usable in ORDER BY but not in WHERE.

Next: Filtering Rows with WHERE