||This question typically appears in the context of producing a <SELECT> list from a database table, where the user wants to show a list of all the unique items in the table. But the same problem can appear in different guises.|
The answer is the
Distinct keyword of SQL.
Consider this table:
Name Group Status
Jones Admin Active
Smith Admin Passive
Wilson Sales Active
Hanson Sales Passive
Barney Systems Active
Halley Systems Active
Let's look at the different kinds of queries we can make on that table and the results we will get.
SQL = "SELECT DISTINCT Group FROM table ORDER BY Group"
Makes sense! We asked for all the possible values of
Group but then used
Distinct to eliminate the duplicates!
SQL = "SELECT DISTINCT Group, Status FROM table ORDER BY Group"
Distinct keyword applies to all fields of the returned recordset! If two records are different in any field, then they are different. Period. And using
Distinct will not magically collapse them into one entry.
Do note the
Systems example! Only one record is returned here, because all (okay, both) the
Systems entries have the same value for
Status so indeed there is only one truly distinct record.