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 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
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
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.
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
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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....
|
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...
|
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...
|
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....
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
| |