Hello List,
Please forgive this rather lengthy post. I thought I had something worked
out to answer Rob's question but when I put it to the test I found what MAY
be a bug in 4.1.1a-alpha-nt-log. Here is what I did.
I created two tables, tablea and tableb. Here are their defs:
mysql show create
SELECT DISTINCT(LEFT (product_number,5)) FROM products.
http://www.mysql.com/doc/en/String_functions.html
hth
Jeff
Personal
Thank you!!!
It works.
Hector
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, October 13, 2003 1:44 PM
To: Personal
Cc: [EMAIL PROTECTED]
Subject: Re: SELECT DISTINCT question
SELECT
fields.
regds,
-Original Message-
From: Sheryl Canter [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 01, 2003 01:56
To: Juan Nin; [EMAIL PROTECTED]
Subject: Re: SELECT DISTINCT question
So depending on which columns you were looking at, two rows might seem
like
duplicates or not? If your
PROTECTED]
Sent: Saturday, March 01, 2003 3:46 AM
Subject: RE: SELECT DISTINCT question
*DISTINCT omits data bases on selected fields only. i.e. if you have
thousand rows having different dates in 'mydate' but all having year 2002,
then SELECT DISITNCT YEAR(mydate) will return only 1 row i.e. 2002
of other ways of handling this.
- Sheryl
- Original Message -
From: Tore Bostrup [EMAIL PROTECTED]
To: Sheryl Canter [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, February 28, 2003 1:41 AM
Subject: Re: SELECT DISTINCT question
DISTINCT works on the result set and across the entire
if others know of other ways of handling this.
- Sheryl
- Original Message -
From: Tore Bostrup [EMAIL PROTECTED]
To: Sheryl Canter [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, February 28, 2003 1:41 AM
Subject: Re: SELECT DISTINCT question
DISTINCT works on the result
Sheryl,
I'm trying to determine which author has the highest royalty percent FOR
EACH PROGRAM, not overall. I'm displaying a list of programs and authors,
and when there is more than one author, I want to show the principal author
(i.e., the one earning the highest royalty percent).
Ok, so your
help.
- Sheryl
- Original Message -
From: Frank Peavy [EMAIL PROTECTED]
To: Sheryl Canter [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, February 28, 2003 11:22 AM
Subject: Re: SELECT DISTINCT question
Sheryl,
I'm trying to determine which author has the highest royalty percent
Canter [EMAIL PROTECTED]; [EMAIL PROTECTED];
Frank Peavy [EMAIL PROTECTED]
Sent: Friday, February 28, 2003 12:34 PM
Subject: Re: SELECT DISTINCT question
At 11:50 -0500 2/28/03, Sheryl Canter wrote:
Frank,
Sorry that I seem to be explaining this so poorly. I'll try to clarify what
I'm trying to do
From: Sheryl Canter [EMAIL PROTECTED]
DISTINCT removes duplicates: rows that are the same in *all* columns.
Would this be all columns in the table, or all columns retrieved by the
SELECT statement?
in the SELECT statement :)
SELECT queries do not alter your rows, you can only do
x, y, z
Form Table
is equivalent with
SELECT x, y, z
FROM Table
GROUP BY x, y, z
HTH,
Tore.
- Original Message -
From: Sheryl Canter [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; Frank Peavy [EMAIL PROTECTED]
Sent: Friday, February 28, 2003 11:50 AM
Subject: Re: SELECT DISTINCT question
?
- Sheryl
mysql
- Original Message -
From: Juan Nin [EMAIL PROTECTED]
To: Sheryl Canter [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, February 28, 2003 2:42 PM
Subject: Re: SELECT DISTINCT question
From: Sheryl Canter [EMAIL PROTECTED]
DISTINCT removes duplicates: rows
Didn't receive answer to message below. Could someone please take a look?
TIA.
- Sheryl
- Original Message -
From: Sheryl Canter [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, February 26, 2003 11:49 AM
Subject: SELECT DISTINCT question
I need help with a query. I have
Sheryl,
Sorry I don't time to fully address your question but I will try to give
you some feedback.
The DISTINCT functionality will give you exact that, every distinct
instance of the columns you SELECTED.
SELECT DISTINCT a.Author FROM author a
Will give you each and every author but only
: SELECT DISTINCT question
Sheryl,
Sorry I don't time to fully address your question but I will try to give
you some feedback.
The DISTINCT functionality will give you exact that, every distinct
instance of the columns you SELECTED.
SELECT DISTINCT a.Author FROM author a
Will give you each and every
On 27-Feb-2003 Sheryl Canter wrote:
snip
SELECT *
FROM programs p, authors a, royalties r
WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID
I could change this to SELECT DISTINCT * ..., but then which author would I
get? If it's always the first encountered row, then could I
Sheryl,
See my comments below...
(1) What is the rule that SELECT DISTINCT uses when deciding which of
multiple instances to return? Does it return the first one?
How it selects the distinct items is outside my area of knowledge.
I would not assume it is the first one or the last one, or anywhere
Frank,
Before you go any further, I would attempt to answer one question.
Are you trying to determine which Author had the highest royalties or are
you trying to determine which Author has the highest royalties in each
program(I am assuming an author can work on multiple programs)? This will
DISTINCT works on the result set and across the entire select list. It will
suppress duplicate result *rows*.
This is an interesting problem when using MySQL due to the lack of support
for nested SELECTs. In other database systems, I'd use a correlated
subquery in the where clause, but with
Say I have a bunch of records in a table with a bunch of fields. One of
those fields is cluster_id. It is not unique. However, I want to make a
unique set. I want one record for each cluster_id value. I don't care
which record that is.
Right now, I do:
SELECT DISTINCT cluster_id FROM my_table
21 matches
Mail list logo
