In this query:

select A, B, C
from
(select A, B, C from table1) as G1,
(select A, B, C from table2) as G2

where G1.A = G2.A
and G1.B = G2.B
and G1.C = G2.C

a record in table1 can be matched with more than one record in table2, and I don't have any other field in the tables that helps me link one G1 record to a specific G2 record.
What can I do to match each G1 record to only one (if any) G2 record (if there are two records in table2 which are identical, I want the query to choose one of them and leave the other one unmatched)?
Another question: is there a way to count records and attribute them a unique number? What is an index?

Thanks in advance

Anna - Verona (Italy)

sorry, you can do nothing

stop thinking of matching records, start thinking of matching column values

if there are two rows in G2 that are identical, you are obviously working without a primary key

SQL is not gonna help you

the problem is more basic than that

I don't think I have any basic problem.
I know what a primary key is. That's why I asked if I can number rows, in order to be able to obtain unique records.
Anyway, I've found out that records in both tables have an identifying column, (whose values are different in the two tables).


Bye.
Anna

yes, i have, and no, you couldn't

Excuse me, R927, could you please cancel your last message (at least in the part where my words are quoted)? I'd prefer not to explain to you why, and I'm sure you won't have any problem in doing this.
Thank you.
Anna

How would you match rows if you would have such a row number? I guess you would only pick the first row, wouldn't you? But then, you have no idea what the first row is and DB2 doesn't guarantee any row order.

How can I make this selection in SQL for DB2?

SELECT ROW_NUMBER() OVER(PARTITION BY A, B, C ORDER BY D) AS RowNo,
A,B,C,D
FROM table1

What I need is to group rows with the same values in columns A, B, C, and to number the rows of each group from 1 .

I know there's the ROW_COUNT function, but I'm not able to use it, I can't find the right syntax, maybe. What about partioning?

Thank you.
(Ciao, Knut, it's been a long time!)
Anna

Is this a trick question? I'm asking because I just cut'n'pasted the statement exactly as you typed it and it's been working fine. It groups the rows for unique combinations of values in columns A, B, C, then orders the rows based on the values in column D and then assigns row numbers in each group, starting with 1. Unless I misunderstand something, this is exactly what you want, right?

...............................................

I use QMF (SQL) for DB2 OZs and these commands are not recognized.

Ciao

Anna - Verona (Italy)

Oh, you are on DB2 z/OS? Which version? DB2 z/OS V9 supports the ROW_NUMBER OLAP function.