| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL to retrieve all distinct records
Mladen,
Do you get a kick out of being rude? Let me break her question down for you. First, since you have problems with the English language, let me help you. The correct grammar usage is "I am a foreigner", not "me foreigner". As far as her question is concerned, it's obvious that she wants to retrieve the distinct values of columns a and b, and list the associated value of column c. What she wants is not possible. She will have to use an aggregate of the values in column c based upon a GROUP BY of columns a and b. But there is a way to say it without being offensive, and clearly, you haven't found it. If you have, you haven't chosen it. You would do well to keep your "smart-based" remarks to yourself.
Alison,
Your question is a good one. To get what you want, Oracle would have to choose between two different values of c for a distinct combination of a and b, and it won't do that. For example, if you have:
a b c - - -
and you wanted to return the distinct a and b with the associated c, Oracle would not be able to choose for you whether you wanted the 34 ounce or 36 ounce bat. However, if you didn't care about the actual value of c, you could use the following:
select distinct
a, b, count(c)
from
baseball_equip
group by
a, b
On the other hand, if you really did want to know what all of the values of c were for each combination of a and b, then you would code this:
select distinct
a, b, c
from
baseball_equip
Lastly, if the reason you only want to list the associated value of c is because you know it is defined by the values of a and b and can only have one value, then I have two points:
HTH, Thanks,
Mike
Michael Milligan
Oracle DBA
Ingenix Corp.
Salt Lake City, Utah 84120
michael.milligan_at_ingenix.com
-----Original Message-----
From: Mladen Gogala [mailto:mladen_at_wangtrading.com]
Sent: Thursday, April 15, 2004 8:41 AM
To: oracle-l_at_freelists.org
Subject: Re: SQL to retrieve all distinct records
On 04/15/2004 09:38:28 AM, Alison Barak wrote:
> Hi,
> can someone assist me with formulating a query to retrieve all distinct
> records. I need to retrieve all three columns a,b &c based on the
distinct
> of the two columns a & b.
Me foreigner. Me have problems with English language. Me not duhveloper.
What the heck does it mean "all three columns a,b &c based on the distinct
of
the two columns a & b"? If you enlighten me and clarify the depths of your
relational thoughts, I might be able to help you. So far, from what you've
already written, I came up with the following script that is guaranteed to
to return all distinct values from the table testing based on the distinct
values of any column, which is, in turn, based on the distinct values of all
other columns:
truncate table testing;
select * from testing;
I assure you, that this script will not return duplicate values from the
table "TESTING",
regardless of what column you base them on and whatever the heck basing
distinct values
on a column is supposed to mean. One of the meaning of the word "base" is
"the body part
used for sitting". I guess you can call me a "smart base".
-- Mladen Gogala Oracle DBA ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Apr 15 2004 - 13:14:10 CDT
 |
 |