Using Distinct in a column

[omnittha]

I would like to pull unique records of a SINGLE column but not the other related columns. When I use DISTINCT, it pulls up all the records which are unique across the columns. Here is the code....

SELECT distinct columnA,columnB,columnC

FROM Table

ORDER by columnA,columnB,columnC;

What I would like to see, is Distinct records from column A, and not from other columns. But I would like to have the records from other columns to show up when I pull distict records from column A.

If I change the sql statement to -

SELECT distinct columnA

FROM Table

ORDER by ColumnA,columnB,columnC;

Get the following error...

ORDERBY clause (columnB,Column C)conflicts with Distinct.

Can some one help me, please?

Regards,

Omnitha

[Krandor]

I tried it on my database. If you have something in the ORDER BY, it has to be in the select also. You can't have a SELECT Distinct on one column and then ORDER BY with multiple columns.

Either add the other columns to the select (I think that would be better) or get rid of them from the ORDER BY.

[NeoPa]

This will be difficult. Mainly because it makes no sense.

How can it make sense to show three columns in a query where only one of them is covered by the DISTINCT qualifier?

Consider what you would expect to be displayed where the following data were encountered :

ColumnA  ColumnB  ColumnC
  A        B         C
  A        B         D
  A        E         F

Can you see why that is nonsensical?

[omnittha]

Appreciate your answer. That makes more sense...now I understand the limitations of Distinct function.

[NeoPa]

I'm glad if I helped you to understand.

Understanding is always preferable to simple solutions.

In situation like this, you may want to consider GROUPing by rather than DISTINCT. You would have a similar problem, but at least you'd have the option of aggregating fields not GROUPed BY (Aggregate functions include Sum(); First(); Last(); Min(); Max(); etc).

Hope this helps with you problem.