Results 1 to 11 of 11

Thread: Record matching

  1. #1
    Join Date
    Apr 2007
    Posts
    51

    Unanswered: Record matching

    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)
    Last edited by annamaria; 02-03-08 at 06:45.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by annamaria
    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)?
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    51
    Quote Originally Posted by r937
    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
    Last edited by annamaria; 02-03-08 at 10:14.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by annamaria
    Have you ever heard of the DISTINCT command?
    Could I write:

    select A,B,C, distinct D?
    yes, i have, and no, you couldn't

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2007
    Posts
    51
    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

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by annamaria
    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).
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Apr 2007
    Posts
    51
    Quote Originally Posted by stolze
    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

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Apr 2007
    Posts
    51

    Lightbulb

    ...............................................
    Last edited by annamaria; 02-07-08 at 10:19.

  10. #10
    Join Date
    Apr 2007
    Posts
    51
    I use QMF (SQL) for DB2 OZs and these commands are not recognized.

    Ciao

    Anna - Verona (Italy)

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Oh, you are on DB2 z/OS? Which version? DB2 z/OS V9 supports the ROW_NUMBER OLAP function.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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