Chapter 13
Distinct Vs Group By
“All things will be clear and distinct to the man who does not hurry; haste is blind and improvident.”
Table of Contents Chapter 13 – Distinct Vs Group By
– Rules of Thumb for DISTINCT Vs GROUP BY
– Quiz – How many rows come back from the Distinct Example
– Answer – How many rows come back from the Distinct
The Distinct Command
DISTINCT eliminates duplicates from the returning in the Answer Set.
Distinct vs. GROUP BY
Distinct and GROUP BY in the two examples return the same answer set.
Rules of Thumb for DISTINCT Vs GROUP BY
Rules for Distinct Vs. GROUP BY
(1) Many Duplicates – use GROUP BY
(2) Few Duplicates – use DISTINCT
(3) Space Exceeded – use GROUP BY
Distinct and GROUP BY work similarly, but utilize both for different situations.
Quiz – How many rows come back from the Distinct Example
How many rows will come back from the above SQL?
Answer – How many rows come back from the Distinct
SELECT Distinct Class_Code, Grade_Pt
FROM Student_Table
ORDER BY Class_Code, Grade_Pt;
Class_Code | Grade_Pt |
? | ? |
FR | 0.00 |
FR | 2.88 |
FR | 4.00 |
JR | 1.90 |
JR | 3.95 |
SO | 2.00 |
SO | 3.80 |
SR | 3.00 |
SR | 3.35 |
No Rows have the exact same Class_Code and Grade_Pt. Each row is Distinct!
How many rows will come back from the above SQL? 10. All rows came back. Why? Because there are no exact duplicates that contain a duplicate Class_Code and. Duplicate Grade_Pt combined. Each row in the SELECT list is distinct.