DISTINCT with multiple columns?

[zaphod]

I need to select unique combinations of 4 columns from one table and insert
them into a new table but I can't think of any way of finding unique
combinations of more than 1 column since SELECT DISTINCT only works on
single columns. Any ideas?

zaphod

[Thomas Bartkus]

"zaphod" <no**@none.com> wrote in message
news:43***********************@ptn-nntp-reader04.plus.net...

I need to select unique combinations of 4 columns from one table and insert them into a new table but I can't think of any way of finding unique
combinations of more than 1 column since SELECT DISTINCT only works on
single columns. Any ideas?

zaphod

CREATE TABLE DistinctItemCombos
SELECT ItemA, ItemB, ItemC, ItemD
FROM {SomeTable}
GROUP BY ItemA, ItemB, ItemC, ItemD

Thomas Bartkus

[Felix Geerinckx]

On 21/11/2005, zaphod wrote:

... since SELECT DISTINCT only works on single columns.

Where did you find that piece of information?
As far as I know, SELECT DISTINCT works on complete rows.

--
felix

[Bill Karwin]

zaphod wrote:

I need to select unique combinations of 4 columns from one table and insert
them into a new table but I can't think of any way of finding unique
combinations of more than 1 column since SELECT DISTINCT only works on
single columns. Any ideas?

I think you have an incorrect understanding of DISTINCT. The DISTINCT
keyword applies to the full set of columns in the select-list.

SELECT DISTINCT a, b, c FROM t

is equivalent to

SELECT a, b, c FROM t GROUP BY a, b, c

In other words, it will output exactly one row for each unique
combination of values in a, b, and c.

Regards,
Bill K.

[zaphod]

Bill Karwin wrote:

I think you have an incorrect understanding of DISTINCT. The DISTINCT
keyword applies to the full set of columns in the select-list.

SELECT DISTINCT a, b, c FROM t

is equivalent to

SELECT a, b, c FROM t GROUP BY a, b, c

In other words, it will output exactly one row for each unique
combination of values in a, b, and c.

Regards,
Bill K.

Sorry, I haven't described my problem clearly. I have a master table
containing an AUTO_INCREMENT 'ID' column and I want to select unique
combinations of 5 columns not including the ID column. After SELECTing these
5 columns I would like to have some way of retaining their original IDs but
including ID in a SELECT DISTINCT would defeat its purpose.

zaphod

[Bill Karwin]

zaphod wrote:

I have a master table
containing an AUTO_INCREMENT 'ID' column and I want to select unique
combinations of 5 columns not including the ID column. After SELECTing these
5 columns I would like to have some way of retaining their original IDs but
including ID in a SELECT DISTINCT would defeat its purpose.

The closest thing I can suggest is to use MySQL's GROUP_CONCAT function
(available in MySQL 4.1):

SELECT GROUP_CONCAT(ID), b, c, d, e, f, g
FROM t
GROUP BY b, c, d, e, f, g;

Regards,
Bill K.

[Rich Ryan]

How about real something simple:

1. Create a table to hold the autoincrement value plus the concatenation of
all the columns that you need. So you end up with a 2 column table. The
second column is just a long string, so you have to CAST some numeric values
into CHAR

2. At this point you can SELECT DISTINCT the second column along with the
autoincrement id.

3. Then you can go back into your original table with a WHERE based on the
auto id.

Get it? If you would post your CREATE TABLE along with some INSERTS, it
would make it easier.

"zaphod" <no**@none.com> wrote in message
news:43***********************@ptn-nntp-reader04.plus.net...

I need to select unique combinations of 4 columns from one table and insert them into a new table but I can't think of any way of finding unique
combinations of more than 1 column since SELECT DISTINCT only works on
single columns. Any ideas?

zaphod