Hi friends!!

            I have this table:

     CODE Name
        a d1
        a
        b d2

            I have to find distinct code but all the rows are coming
i am doing like : SELECT DISTINCT(CODE), NAME FROM TABLENAME;
            But result is coming like:
      CODE Name
        a
        a d1
        b d2
            while I just expecting two rows with unique codes.
help me please here.
     Thanks Readers!!!!!!!


DPK..

__________________
DPK..

well.. you in fact have 3 codes (empty, d1 and d2).. if you want to avoid empty fields you will have to add a where to avoid them....

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

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 :
a
b

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 <> ''

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
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
================================================== =========

I thought DISTINCT returned distinct rows, not distinct columns. Since the following three rows aren’t any of them the exact same:     all 3 are returned.

But further, if the SQL had operated as intended, the value of the NAME column would be poorly defined. He would have gotten [u]a</u> name value, but not one that had much significance.

Thanks Dparsons!!!

    But as you gave me the query, It will not work for me because if I have a row that does not has NAME i mean it has <BLANK> in NAME field then I will miss that row and I want to fetch all the codes.

   I hope you get me..

Thanks again!!!

DPK..

Thanks you all to give your time!!

   well I have code it by using this:

select distinct id,name from temp1
where name is not null
group by id,name
union all
select id,name from temp1
where id in ( select id from temp1 group by id having count(id) = 1)
and name is null;


Thanks again!!

DPK..