Using the DISTINCT modifier in SQL
For the novice SQL programmer, the DISTINCT modifier component of a SELECT statement can cause confusion. Let us consider the following table:
The SELECT action will return unique values for all the columns in the expression. For example:
SELECT DISTINCT SubsectionID,Subsection from Subsections
will return all 7 rows. But:
SELECT DISTINCT Subsection from Subsections
will return 4 rows, containing Salaries, Wages, Sales and Marketing. The same logic will hold true for tables with more columns. My suggestion is that SQL programmers use GROUP BY clauses to remove any doubt from statement. To return the 4 unique rows from the Subsection column, one can use:
SELECT Subsection from Subsections GROUP BY Subsections
And in order to try and replicate the first statement, one could use MIN to return the minimum SubsectionID for each Subsection. eg>
SELECT MIN(SubsectionID) as MinSubID, Subsection from Subsections GROUP BY Subsections
Next, I'd advise novices to experiment with GROUP BY clause in Access. This can be done by creating a new query in Access, then selecting the apporpriate tables and columns. Then, right-click a column and select "Totals" as seen in the screenshot below:
For the most part, there is probably no difference in performance between using DISTINCT or GROUP BY. Although, GROUP BY could be slightly more efficient in complex queries. DISTINCT is separated into 2 processes by SQL - one to reduce the rows and one to calculate the returned results.
For further reading on the basics of SQL:
http://www.w3schools.com
http://www.sqlcourse.com
