“A bird does not sing because it has the answers, it sings because it has a song.”
- Anonymous
The GROUP BY also eliminates duplicates from returning in the Answer Set.
Distinct and GROUP BY in the two examples return the same answer set.
How many rows will come back from the above SQL?
SELECTDistinct Class_Code, Grade_Pt
FROMStudent_Table
ORDER BYClass_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.
The LIMIT command is designed to bring back the top n rows.
We used the LIMIT command with an ORDER BY statement to get the top students with the highest grade point.
We used the TABLESAMPLE command in order to return only 5 rows from the table.
The Collect_List function will bring back all values in a table even if they repeat, but it excludes null values. The Employee_Table has 9 rows with one row having a null value for Dept_No. There are eight values that came back above when we performed a Collect_List on Dept_No. A Collect Set function will not bring back duplicates or null values.