sql Select multiple columns of a table

Question

I am trying to a select from a table in oracle DB.

If I do distinct in select statement on single column it works fine but for multiple columns this doesn't seem to work.

for example.

select DISTINCT col1, col2, col3, col4, col5, col6 from table1

returns the same result as.

select * from table1

Column 2, Column3 has duplicate values. What I want is values of all the above column values but not the duplicate values of column2 and column3.

Can you please help. Your help will be appreciated.

Thanks

Can you give is a sample of what is in the table? If you get the same results as a `SELECT *` then all your records in COL1 are distinct. — ProfessionalAmateur, Jan 04 '12 at 22:08
Do you want to check [this SO question](http://stackoverflow.com/questions/54418/how-do-i-or-can-i-select-distinct-on-multiple-columns-postgresql) ? looks very similar — ring bearer, Jan 04 '12 at 22:09
Keep in mind that the DISTINCT is applied across the entire set of columns you're returning, not just the first column. So, for example `(1,2,3,4,5,6)` is distinct from `(1,2,3,4,5,7)` because the sixth column is different between the two rows. — Joe Stefanelli, Jan 04 '12 at 22:10
Thanks all for your responses, really appreciate it. Now trying to figure how can i solve the problem — Nomad, Jan 04 '12 at 22:14
@Nomad: Perhaps you can edit the question with what exactly your problem is (what your desired output is) — ypercubeᵀᴹ, Jan 04 '12 at 22:32
@Nomad: Try putting 5-6 rows of the original table and the rows you want in the output. The actual data. — ypercubeᵀᴹ, Jan 04 '12 at 23:30
What do you want to see instead of the duplicated values? Is this a data selection issue or a data display issue? If the latter, what client are you using to render your result set? — APC, Jan 05 '12 at 05:52

score 5 · Accepted Answer · answered Jan 04 '12 at 22:10

5

DISTINCT eliminates redundant data for each row and the columns selected. So if you select all columns of a table using DISTINCT, only rows which columns contain the exact same data are aggregated.

answered Jan 04 '12 at 22:10

Chris

7,229
7
41
57

Thanks Chris for your response. – Nomad Jan 04 '12 at 22:15
Please do not edit questions. Otherwise this becomes a chat or message board ;) Just open up a new one and take some time describing your problem with data samples etc. – Chris Jan 05 '12 at 09:14

score 1 · Answer 2 · answered Jan 06 '12 at 12:48

Try the following:

SELECT DISTINCT COL1 AS COL1, NULL AS COL2, NULL AS COL3, NULL AS COL4,
       NULL AS COL5, NULL AS COL6 FROM TABLE1
UNION ALL
SELECT NULL AS COL1, DCOL2 AS COL2, NULL AS COL3, NULL AS COL4,
       NULL AS COL5, NULL AS COL6 FROM (SELECT DISTINCT COL2 AS DCOL2
                                          FROM TABLE1)
UNION ALL
SELECT NULL AS COL1, NULL AS COL2, DCOL3 AS COL3, NULL AS COL4,
       NULL AS COL5, NULL AS COL6 FROM (SELECT DISTINCT COL3 AS DCOL3
                                          FROM TABLE1)
UNION ALL
SELECT NULL AS COL1, NULL AS COL2, NULL AS COL3, DCOL4 AS COL4,
       NULL AS COL5, NULL AS COL6 FROM (SELECT DISTINCT COL4 AS DCOL4
                                          FROM TABLE1)
UNION ALL
SELECT NULL AS COL1, NULL AS COL2, NULL AS COL3, NULL AS COL4,
       DCOL5 AS COL5, NULL AS COL6 FROM (SELECT DISTINCT COL5 AS DCOL5
                                           FROM TABLE1)
UNION ALL
SELECT NULL AS COL1, NULL AS COL2, NULL AS COL3, NULL AS COL4,
       NULL AS COL5, DCOL6 AS COL6 FROM (SELECT DISTINCT COL6 AS DCOL6
                                           FROM TABLE1);

Share and enjoy.

sql Select multiple columns of a table

2 Answers2