473,324 Members | 2,548 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

DISTINCT with multiple columns?

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
Nov 23 '05 #1
6 131091
"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
Nov 23 '05 #2
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
Nov 23 '05 #3
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.
Nov 23 '05 #4
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
Nov 23 '05 #5
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.
Nov 23 '05 #6
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

Nov 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Dennis | last post by:
In CSS3 it looks like we'll have multiple column flowing of text (newspaper style) in which the number of columns can be determined automatically given the available horizontal space....
7
by: Billy Jacobs | last post by:
I am using a datagrid to display some data. I need to create 2 header rows for this grid with columns of varying spans. In html it would be the following. <Table> <tr> <td colspan=8>Official...
0
by: David Londeck | last post by:
I am using Visual Basic 2003 and I have written a notepad like application using the RichTextBox control. I am having trouble trying to emulate Microsoft Words text block copy/paste feature. In...
3
by: Jane | last post by:
Hi, If anyone could help me with this, I greatly appreciate it. I would like to know how I can display data from a db in multiple columns rather than have it displayed in 1 column per recordset....
4
by: Cezar | last post by:
Hi, I need to display the content of an ArrayList on multiple columns, like data1 data2 data3 data4 data5 data6 data7 data8 ---- or data1 data3 data5 data7
2
by: Simon Harris | last post by:
Hi All, I have an app which requires a list of coutries to be displayed, so far I have a datagrid which contains displays the countries Ok, along with flag images, these are also links to my...
3
by: simchajoy2000 | last post by:
Hi, I am trying to use a VB.NET listview object to display information from a datatable. I need to have two columns of information but I don't want the user to be able to select each column...
2
by: scratchadere | last post by:
I am tryin to partition a listbox into multiple columns.Here is the real problem.I am getting data from MS ACCESS database.I want to display it juz like how an email account displays unread messages...
2
by: ray well | last post by:
i need to display 2 columns of data in a list box. how would i set this up IN CODE. say my table is tblNames, and i have 2 fields, FirstName, LastName, and want the data to show up in 2...
3
by: Will | last post by:
Can someone help with code to delete multiple columns from an excel spreadsheet? I know which columns I need to delete. The code below will delete a single column but I'm not sure how to delete...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.