“A bird does not sing because it has the answers, it sings because it has a song.”
- Anonymous
DISTINCT eliminates duplicates from returning in the Answer Set.
Both examples produce the exact same result
Class_Code
FR
JR
SO
SR
?
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 in the two examples return the same answer set.
How many rows will come back from the above SQL?
SELECT Distinct Class_Code, Grade_Pt
FROM Student_Table
ORDER BY Class_Code, Grade_Pt ;
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.
In the above example, we brought back 3 rows only. This is because of the TOP 3 statement which means to get an answer set, and then bring back the first 3 rows in that answer set. Because this example does not have an ORDER BY statement, you can consider this example as merely bringing back 3 random rows.
In the above example, we brought back 3 rows only. This is because of the TOP 3 statement which means to get an answer set, and then bring back the first 3 rows. Because this example uses an ORDER BY statement, the data brought back is from the top 3 students with the highest Grade_Pt. This is the real power of the TOP command. Use it with an ORDER BY!
Both queries above bring back the top 3 students with the highest grade_pt. The TOP command is designed to bring back the top n rows. The LIMIT clause is used more often if you merely want to see a quick sample, but both techniques will work with an ORDER BY statement and both can utilize an ORDER BY statement in the creation of a view.