Select Distinct Values

Easy3 min Save for later

Question

Interview insights are lockedSee how often this is asked and which companies asked it with Buncho Pro.

Problem

You have a students_info table.

Table: students_info

id	name	grade
1	Ana	10
2	Raj	9
3	Mei	10
4	Sam	11
5	Zoe	9

Task

Write a query that returns every unique grade in ascending order, and a second query that returns how many unique grades there are.

Expected output

grade
9
10
11

The count query should return 3.

Work out your queries on your own, then open Answers & Optimization to compare.

Hints

Answers

2 ways to solve it

A basic answer first, then the optimized one.

Basic
Optimized

Quick answer

DISTINCT removes duplicate rows across all the selected columns, not just the first one.
COUNT(DISTINCT col) counts unique non-NULL values.
DISTINCT and GROUP BY give the same result here and usually the same plan.

1Basic

DISTINCT with ORDER BY

1SELECT DISTINCT grade2FROM students_info3ORDER BY grade;4 5SELECT COUNT(DISTINCT grade) AS unique_grades6FROM students_info;

Result: 9, 10, 11
unique_grades = 3

2Optimized

Index the column, skip work you do not need

1-- With this index the engine can read the index only, not the whole table2CREATE INDEX idx_students_grade ON students_info (grade);3 4SELECT grade5FROM students_info6GROUP BY grade7ORDER BY grade;8 9-- Only need to know whether a value exists? Stop at the first match10SELECT EXISTS (SELECT 1 FROM students_info WHERE grade = 10) AS has_grade_10;

Tip: drop ORDER BY when the order does not matter, sorting is the costly part
DISTINCT on many columns is slow on big tables, check if you really need every column

Spotted a mistake in these answers?

Report a problem