Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2010
    Posts
    5

    Unanswered: select self join, distinct values

    I have no idea what sql command will return the results I need from 1 table.

    example of table and values

    col1 col2 col3(unique values)
    1 1 abc
    1 1 dev
    1 2 rst
    1 3 ndb
    1 3 mnv

    I need an sql query, that returns the distinct combined values of col1
    and col2 and the first value of that distinct combination from col3.


    return values should be;
    1 1 abc
    1 2 rst
    1 3 ndb

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dform View Post
    the first value of that distinct combination from col3.
    Please define "first". Rows in a table do not have any intrinsic ordering.

  3. #3
    Join Date
    Sep 2010
    Posts
    5
    First row meaning. Return a c value for each distinct a,b combination

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please define "first". Rows in a table do not have any intrinsic ordering.
    Yes, many experts metined repeatedly that point.

    Because, DB2(and almost all relational databases) don't expect any order of rows in a table,
    even if you supplied or showed rows with specific order.

    The logical order of rows have meaning only in a query using ORDER BY clause explicitly.


    Here are examples.

    Exampel 1:
    'ndb' was selected for a row (col1, col2) = (1, 3).
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT col1 , col2 , col3
     FROM  (SELECT s.*
                 , ROW_NUMBER()
                     OVER(PARTITION BY col1, col2) rn
             FROM  dform.sample_data s
           ) t
     WHERE rn = 1
    ;
    ------------------------------------------------------------------------------
    
    COL1        COL2        COL3   
    ----------- ----------- -------
              1           1 abc    
              1           2 rst    
              1           3 ndb    
    
      3 record(s) selected.

    Exampel 2:
    'mnv' was selected for a row (col1, col2) = (1, 3).
    Code:
    ------------------------------ Commands Entered ------------------------------
    ALTER TABLE dform.sample_data
    ADD PRIMARY KEY (col1 , col2 , col3)
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT col1 , col2 , col3
     FROM  (SELECT s.*
                 , ROW_NUMBER()
                     OVER(PARTITION BY col1, col2) rn
             FROM  dform.sample_data s
           ) t
     WHERE rn = 1
    ;
    ------------------------------------------------------------------------------
    
    COL1        COL2        COL3   
    ----------- ----------- -------
              1           1 abc    
              1           2 rst    
              1           3 mnv    
    
      3 record(s) selected.

    DDL and data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE dform.sample_data
    ( col1 INTEGER    NOT NULL
    , col2 INTEGER    NOT NULL
    , col3 VARCHAR(7) NOT NULL
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO dform.sample_data
    VALUES
      (1 , 1 , 'abc')
    , (1 , 1 , 'dev')
    , (1 , 2 , 'rst')
    , (1 , 3 , 'ndb')
    , (1 , 3 , 'mnv')
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

  5. #5
    Join Date
    Sep 2010
    Posts
    5
    Wow, I am not familiar with OLAP functions, over(partition function. I must check the version to find out if it is supported. Is your first example a solution, I cannot test this at the moment.


    Are there other ways to perform return these same results with basic inner/outer joins or unions with a dummy table?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is your first example a solution, ...
    It was by chance that the result of Example 1 looks like what you wanted.

    Please compare the results of Example 1 and Example2.
    I want to show the result was changed by adding PRIMARY KEY(and created corresponding index implicitly),
    if ORDER BY was not specified,
    even when two queries and data were completely identical.
    Last edited by tonkuma; 09-11-10 at 03:04.

  7. #7
    Join Date
    Sep 2010
    Posts
    5

    thank you.

    The result returned by either example for col 3 is sufficient for the purpose of this exercise. It would be best to have the same results without using the OLAP function.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It would be best to have the same results without using the OLAP function.
    Why is it best?

  9. #9
    Join Date
    Sep 2010
    Posts
    5
    The olap function is probably not supported in the version I use.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are your DB2 version/release and platform OS?

    ROW_NUMBER() OVER(...) is supported on
    DB2 for LUW Version 8.2
    DB2 Version 9.1 for z/OS
    DB2 for i5/OS Version 5 Release 4

Tags for this Thread

Posting Permissions

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