Chapter 6 – Distinct, Group By and TOP

“If I have seen farther than others, it is because I was standing on the shoulders of giants”

- – Isaac Newton

The Distinct Command

DISTINCT eliminates duplicates from returning in the Answer Set.

Distinct vs. GROUP BY

Distinct and GROUP BY in the two example return the same answer set.

Quiz – How many rows come back from the Distinct?

How many rows will come back from the above SQL?

Answer – How many rows come back from the 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.

TOP Command

In the above example, we brought back 4 rows only. This is because of the TOP 4 statement which means to get an answer set, and then bring back the first 4 rows in that answer set. Because this example does not have an ORDER BY statement, you can consider this example as merely bringing back 4 random rows.

TOP Command with an ORDER BY Statement

In the above example, we brought back 4 rows only, but we used an ORDER BY statement in the query. TOP goes with ORDER BY like bread goes with butter. The answer set is sorted from the ORDER BY statement first and then the TOP 4 rows came back.

Just Place the TOP n in front of any Query

You can take any query and put the TOP n just after the SELECT and the TOP rows from the answer set return. In the above example, we placed a TOP 3 in front of a normal query. Only 3 rows returned.