quote:Originally posted by gbianchi
well.. you in fact have 3 codes (empty, d1 and d2)..
To better elaborate that you do, in fact, have 3 distinct rows but only 2 distinct codes.
If you execute this query:
SELECT Distinct Code from <table>
You will return :
but since name contains 3 unique values, d1, d2, and an empty string you get the result set you mentioned in your original post. DISTINCT is not an Aggregate function as you have tried to do in your query and, as such, you cant simply execte distinct on one column and not the rest of the columns in your query.
To get your desired results your query needs to be:
SELECT DISTINCT Code, Name from <table> where Name <> ''
Read this if you want to know how to get a correct reply for your question:
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0