| Answer: |
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"
results:
Admin
Sales
Systems
|
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"
results:
Admin, Active
Admin, Passive
Sales, Active
Sales, Passive
Systems, Active
|
Aha! The 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.
Okay?
|
