When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

4GuysFromRolla.com : ASP FAQS : Databases, Queries


Question:

How can I eliminate duplicates from a recordset?


[Print this FAQ]

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?


FAQ posted by Bill Wilkinson at 10/17/2000 8:09:42 PM to the Databases, Queries category. This FAQ has been viewed 50,160 times.

Do you have a FAQ you'd like to suggest? Suggestions? Comments? If so, send it in! Also, if you'd like to be a FAQ Admin (creating/editing FAQs), let me know! If you are looking for other FAQs, be sure to check out the 4Guys FAQ and Commonly Asked Messageboard Questions!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article