RE: SQL to retrieve all distinct records
- From: Michael Milligan <Michael.Milligan@xxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>,"'ambarak@xxxxxxxxxxx'" <ambarak@xxxxxxxxxxx>
- Date: Thu, 15 Apr 2004 11:48:13 -0500
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
- - -
bat 34" 34 ounce
bat 34" 36 ounce
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:
1) It's possible that the table structure has an inappropriate functional
dependency of column c on columns a and b.
2) Using the second query I listed would get you the results you wanted
anyway, if there is only one value of c for each unique combination of a and
b.
HTH,
Thanks,
Mike
Michael Milligan
Oracle DBA
Ingenix Corp.
Salt Lake City, Utah 84120
michael.milligan@xxxxxxxxxxx
-----Original Message-----
From: Mladen Gogala [mailto:mladen@xxxxxxxxxxxxxxx]
Sent: Thursday, April 15, 2004 8:41 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
