“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 three rows. The TOP command is designed to bring back the top n rows. TOP allows the data to be sorted first so it can bring back the top 3 students with the highest grade points. The SAMPLE command will bring back three rows, but it doesn't utilize the sort. Sample is a random sample, but TOP can be more.
The TOP command is designed to bring back the top n rows. TOP allows the data to be sorted first so it can bring back the top 3 students with the highest grade points. The SAMPLE command will bring back three rows, but it doesn't utilize the sort. Sample is a random sample, but TOP can be more. The data above is only sorted by Grade_Pt after the random sample returns. A TOP 3 command would have sorted all the students by Grade_Pt first and then given us the top three.
By using the TOP WITH TIES Command, this will bring in the TOP amount along with ANY ties. So while you might only ask for the top 2 with ties, you might get 4 rows back. Why did 4 rows return here? Which row came back first? Four rows returned with the first row coming back as a NULL for Class_Code. Then the next row returned was one of the Freshman. There were two other Freshman that tie. All ‘FR’ come back in a tie!
Because of the location of TOP within the SELECT and the elimination of some of the rows, TOP is NOT compatible with the following SQL Constructs.
* DISTINCT and QUALIFY
* WITH and WITH BY
* SAMPLE