# Thread: Distinct combination of 3 columns

1. Registered User
Join Date
Aug 2007
Posts
5

## Unanswered: Distinct combination of 3 columns

Hi All,
I have to select eight columns from a table.
the where condition should be the combination of three columns (out of those eight columns).
i tried group by but with other five columns it wont work.

its like
select c1,c,c3,c4,c5,c6,c7,c8 from table where [ unique or distinct combination of c3,c4,c5 ]

kindly let me know the where condition . DB2 version 7 or 8

Thanks
Ashok

2. Registered User
Join Date
Jun 2004
Posts
115
Hi

select c1,c,c3,c4,c5,c6,c7,c8,
row_number()
over(partition by c3,c4,c5
order by c3,c4,c5 nulls last)
as rn
from table
where rn=1

regards
Hrishy

3. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
I have no clue what you want to do. You want to return a row for each unique combination of c3, c4, and c5? What happens if there are multiple rows with that combination - which one do you pick? What happens if there is no row with that combination - you want to have this in the result or not?

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```select min(c1),max(c2)
, c3,c4,c5
, min(c6),max(c7),avg(c8)
from table
group by c3,c4,c5```

5. Registered User
Join Date
Aug 2007
Posts
5
Originally Posted by r937
Code:
```select min(c1),max(c2)
, c3,c4,c5
, min(c6),max(c7),avg(c8)
from table
group by c3,c4,c5```

I Cant use group functions in all other columns .

6. Registered User
Join Date
Aug 2007
Posts
5
Originally Posted by db2hrishy
Hi

select c1,c,c3,c4,c5,c6,c7,c8,
row_number()
over(partition by c3,c4,c5
order by c3,c4,c5 nulls last)
as rn
from table
where rn=1

regards
Hrishy

What is that row_number() and partition , do we have anything like that in DB2?

7. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by ashoash
I Cant use group functions in all other columns .
why not?

i don't see anything about c1, c2 ... that makes them special

8. Registered User
Join Date
Aug 2007
Posts
5
Originally Posted by stolze
I have no clue what you want to do. You want to return a row for each unique combination of c3, c4, and c5? What happens if there are multiple rows with that combination - which one do you pick? What happens if there is no row with that combination - you want to have this in the result or not?
ok , assume there are 8 rows with 8 columns (c3,c4,c5 are three columns in that 8). now i need to write a DB2 query to find out rows which have unique combination of C3,C4,C5 (distinct of 3 columns) , if there are 4 rows in that 8 rows which have same combination of c3,c4,c5 (same order) then the resultset should have only one row out of those 4 rows(in fact any row) + other 4 rows.

how come there cud'nt be a resultset - there should be a result set .

C3 C4 C5
1 2 3
2 3 1
3 4 5
2 3 1
1 2 3
5 6 7
3 4 5
7 6 5

Ans - Resultset

C3 C4 C5
1 2 3
2 3 1
3 4 5
5 6 7
7 6 5

9. Registered User
Join Date
Aug 2007
Posts
5
Originally Posted by r937
why not?

i don't see anything about c1, c2 ... that makes them special
But i want to get the exact values and not the sum or avg or count
Moreover Sum , count and Av will give you only one row.

Example:

C3 C4 C5
1 2 3
2 3 1
3 4 5
2 3 1
1 2 3
5 6 7
3 4 5
7 6 5

Ans - Resultset

C3 C4 C5
1 2 3
2 3 1
3 4 5
5 6 7

10. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
if you don't care which row, what's wrong with MIN values for the other columns?

why? why? why?

so far you have only talked about c1, c2, c3... -- this is way too generic for anybody to understand what you're really doing

11. Registered User
Join Date
Jun 2004
Posts
115
Originally Posted by ashoash
What is that row_number() and partition , do we have anything like that in DB2?

Hi

row_number is a OLAP function.(there are many others available )

Have a look at this function here
http://www.ibm.com/developerworks/db.../0110lyle.html

regards
db2hrishy

12. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
I'm seriously questioning the usability of such a scenario...

Anyway, what you want to do is straight-forward:
1. select all the unique combinations of the values in columns c2, c3, and c4
2. select any row that has this combination

Code:
```SELECT t2.*
FROM   ( SELECT DISTINCT c2, c3, c4
FROM ... ) AS t1,
LATERAL ( SELECT *
FROM ...
WHERE  ( c2, c3, c4 ) = ( t1.c2, t1.c3, t1.c4 )
FETCH FIRST 1 ROW ONLY ) AS t2```
(The ROW_NUMBER() is more elegant.)

how come there cud'nt be a resultset - there should be a result set .
What do you mean with that? I said something completely different.

Rudy, using column/aggregation functions is not an option because it wouldn't give you the values of a single row but rather the combination of multiple rows.

p.s: There are no such things as "group functions".

13. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
yes, knut, i understand about how the aggregate functions would not necessarily yield an actual row

my question still stands: why is this not acceptable?

14. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
Originally Posted by r937
yes, knut, i understand about how the aggregate functions would not necessarily yield an actual row

my question still stands: why is this not acceptable?
Good question: I had the same because the whole scenario doesn't make much sense to me.

15. Registered User
Join Date
Dec 2005
Posts
273
if the columns are CHAR-type, a construct like:

SELECT c3,c4,c5,MIN( c1 !! c2 !! c6 !! c7 !! c8 )
FROM table
GROUP BY c3,c4,c5 ;

might be useful.

You get the values of ONE row for each distinct c3,c4,c5 combination.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•