By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,910 Members | 1,036 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,910 IT Pros & Developers. It's quick & easy.

Using Distinct in a column

P: 7
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....
Expand|Select|Wrap|Line Numbers
  1. SELECT distinct columnA,columnB,columnC
  2.  
  3. FROM Table
  4.  
  5. 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 -
Expand|Select|Wrap|Line Numbers
  1. SELECT distinct columnA
  2.  
  3. FROM Table
  4.  
  5. ORDER by ColumnA,columnB,columnC;
Get the following error...

ORDERBY clause (columnB,ColumnC)conflicts with Distinct.

Can some one help me, please?

Regards,

Omnitha
Aug 19 '08 #1
Share this Question
Share on Google+
4 Replies


P: 50
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.
Aug 19 '08 #2

NeoPa
Expert Mod 15k+
P: 31,418
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 :
Expand|Select|Wrap|Line Numbers
  1. ColumnA  ColumnB  ColumnC
  2.   A        B         C
  3.   A        B         D
  4.   A        E         F
Can you see why that is nonsensical?
Aug 20 '08 #3

P: 7
Appreciate your answer. That makes more sense...now I understand the limitations of Distinct function.
Aug 22 '08 #4

NeoPa
Expert Mod 15k+
P: 31,418
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.
Aug 23 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.