Getting rid of duplicates

[Joe Narissi]

I have a table, TEST_TABLE, with 6 columns (COL1, COL2, COL3, COL4,
COL5, COL6).... I need to be able to select all columns/rows where
COL3, COL4, and COL5 are unique....

I have tried using DISTINCT and GROUP BY, but both will only allow me
to access columns COL3, COL4, and COL5..... i need access to all
columns...I just want to get rid of duplicate rows (duplicates of
COL3, COL4, and COL5)...
Thanks in advance.

Joe

[David Portas]

If a row is duplicated on (col3, col4, col5) which values do you want for
col1 and col2?

Here's some example data:

CREATE TABLE Sometable (col1 INTEGER NOT NULL, col2 INTEGER NOT NULL, col3
INTEGER NOT NULL, col4 INTEGER NOT NULL, col5 INTEGER NOT NULL /* PRIMARY
KEY ??? */)

INSERT INTO Sometable VALUES (1,2,3,4,5)
INSERT INTO Sometable VALUES (2,1,3,4,5)

If you don't care which values go into col1 and col2:

SELECT MIN(col1) AS col1, MIN(col2) AS col2,
col3, col4, col5
FROM Sometable
GROUP BY col3, col4, col5

If you want just one row from the table for each value of (col3, col4,
col5):

SELECT MIN(S1.col1) AS col1, S1.col2, S1.col3, S1.col4, S1.col5
FROM Sometable AS S1
JOIN
(SELECT MIN(col2) AS col2, col3, col4, col5
FROM Sometable
GROUP BY col3, col4, col5) AS S2
ON S1.col2 = S2.col2
AND S1.col3 = S2.col3
AND S1.col4 = S2.col4
AND S1.col5 = S2.col5
GROUP BY S1.col2, S1.col3, S1.col4, S1.col5

Try out one of these two queries. If you need more help, please post DDL and
sample data (CREATE and INSERT statements as above) and show your required
result.

--
David Portas
------------
Please reply only to the newsgroup
--