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

Hi

How about

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

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?

I Cant use group functions in all other columns .

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

why not?

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

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

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

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

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

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

(The ROW_NUMBER() is more elegant.)

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".

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.

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.