db2 select distinct rows, but select all columns

Question

Experts, I have a single table with multiple columns. col1, col2, col3, col4, col5, col6

I need to select distinct (col4), but I need all other columns also on my output.

If I run, this ( select distinct(col4 ) from table1 ), then I get only col4 on my output.

May I know, how to do it on db2?.

Thank you

Assuming the following table: `create table T(c4 int,c5 int); insert into T(c4,c5) values (1,2), (1,3)`. What would the output from your query be? — Lennart - Slava Ukraini, Aug 12 '14 at 17:53
I think you have to revise your question. Since every distinct value on col4 column may have different values on other columns, which value do you expect to be shown for a distinct value? ex. If you have two columns and 4 records like this: 1-A, 1-B, 2-C, 3-D and you want to show distinct values for first column, you will get only 1 and 2, but which value do you want to see next by this column? Because for the value 1, there are A or B, and for the value 2 there are C and D. — ramazan polat, Aug 13 '14 at 13:54

score 2 · Answer 1 · edited May 25 '16 at 21:16

2

You simply do this...

Select * From Table1 Where col4 In (Select Distinct(col4) From Table1)

edited May 25 '16 at 21:16

Simon Schüpbach

answered May 25 '16 at 20:25

Mike

Marcin Szymczak · Answer 2 · 2014-08-12T14:53:53.677

1

I'm not sure if you will be able to do this.

You might try to run group by on this column. You will be able to run some aggregate functions on other columns.

select count(col1), col4 from table1 group by (col4);

edited Aug 12 '14 at 14:53

answered Aug 12 '14 at 14:48

Marcin Szymczak

Khan M · Answer 3 · 2018-02-08T18:51:23.663

0

none of the answers worked for me so here is one that i got working. use group by on col4 while taking max values of other columns

select max(col1) as col1,max(col2) as col2,max(col3) as col3
  , col4
  from 
    table1
  group by col4

edited Feb 08 '18 at 18:51

answered Feb 08 '18 at 18:36

Khan M

2

Um, are you sure that's what you want? The values of the non-`col4` columns are likely from different rows. Normally people in this situation want some form of [tag:greatest-n-per-group] result. – Clockwork-Muse Feb 08 '18 at 21:09

score -2 · Answer 4 · answered Aug 12 '14 at 15:17

At least in DB2, you can execute

SELECT
  DISTINCT *
FROM
<YOUR TABLE>

Which will give you every distinct combination of your (in this case) 6 columns.

Otherwise, you'll have to specify what columns you want to include. If you do that, you can either use select distinct or group by.

4 Answers4