Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    7

    Unanswered: Selecting records that appear only once in the table

    Hi all,

    I am cracking my head over this query. I have a table that contains records made by a user. For example,

    A B User Date
    XX 1.30 TIM 12/05/09
    GG 3.02 TIM 12/05/09
    HH 1.69 TIM 12/05/09
    RR 5.01 TIM 13/05/09
    SS 12.11 TIM 23/06/09
    YY 8.90 TIM 23/06/09
    VV 68.30 TIM 30/11/09
    PP 25.30 TIM 30/11/09
    KK 8.80 TIM 15/12/09

    The result should be
    A B User Date
    RR 5.01 TIM 23/06/09
    KK 8.80 TIM 15/12/09

    because the dates for these 2 records only appear once in the table.

    I am using i5/OS v5r4.

    Thanks.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    select * from table-name a where exists
    (
    select date_col, count(*) from table-name b
    where a.date_col = b.date_col
    group by date_col
    having count(*) = 1
    )
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It can be a little shorter, like this:
    Code:
    SELECT *
      FROM table_name a
     WHERE EXISTS
           (SELECT 0
              FROM table_name b
             WHERE b.date_col = a.date_col
            HAVING COUNT(*) = 1
           )
    ;
    From "DB2 for i5/OS SQL Reference Version 5 Release 4"
    EXISTS predicate

    >>--- EXISTS --- ( fullselect ) ---<<
    ...
    ...

    The values returned by the fullselect are ignored.
    having-clause

    >>--- HAVING --- search-condition ---<<

    The HAVING clause specifies an intermediate result table that consists of those
    groups of R for which the search-condition is true. R is the result of the previous
    clause of the subselect. If this clause is not GROUP BY, R is considered a single
    group with no grouping expressions
    .

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I compared cost of some queries on DB2 9.7 for Windows.
    Because, I have no access to DB2 for iSeries.

    Although, sometimes, it is meaningless to compare costs of different queries,
    you might get some hints for effectiveness of queries by comparing costs.

    But, please note that these tests were done on very poor(memory, processor and disk configuration) environment and with litte data.

    Costs of queries are.....
    a) EXISTS > b) IN > c) NOT EXISTS
    But, the differences are small.

    a) EXISTS
    Code:
    SELECT *
      FROM table_name a
     WHERE EXISTS
           (SELECT 0
              FROM table_name b
             WHERE b.date_col = a.date_col
            HAVING COUNT(*) = 1
           )
    ;
    b) IN
    Code:
    SELECT *
      FROM table_name
     WHERE date_col IN
           (SELECT date_col
              FROM table_name
             GROUP BY date_col
            HAVING COUNT(*) = 1
           )
    ;
    c) NOT EXISTS
    Code:
    SELECT *
      FROM table_name t1
     WHERE NOT EXISTS
           (SELECT *
              FROM table_name t2
             WHERE t2.date_col = t1.date_col
               AND NOT
                   (t2.a = t1.a AND t2.b = t1.b)
           )
    ;
    Followings are much less costs than previous three queries.
    Costs are....
    d) GROUP BY and HAVING < f) COUNT(*) OLAP specification < e) ROW_NUMBER() OLAP specifications
    But, the differences of the three queries are small.

    Note: f) COUNT(*) OLAP specification may not work on DB2 for iSeries.

    d) GROUP BY and HAVING
    Code:
    SELECT MAX(a)        AS a
         , MAX(b)        AS b
         , MAX(user_nme) AS user_nme
         , date_col
      FROM table_name
     GROUP BY
           date_col
    HAVING COUNT(*) = 1
    ;
    e) ROW_NUMBER() OLAP specifications
    Code:
    SELECT a , b , user_nme , date_col
      FROM (SELECT t.*
                 , ROW_NUMBER()
                      OVER(PARTITION BY date_col
                               ORDER BY a ASC  , b ASC ) AS rn_asc
                 , ROW_NUMBER()
                      OVER(PARTITION BY date_col
                               ORDER BY a DESC , b DESC) AS rn_desc
              FROM table_name t
           ) s
     WHERE rn_asc  = 1
       AND rn_desc = 1
    ;
    f) COUNT(*) OLAP specification
    (This may not work on DB2 for iSeries.)
    Code:
    SELECT a , b , user_nme , date_col
      FROM (SELECT t.*
                 , COUNT(*)
                      OVER(PARTITION BY date_col ) AS cnt
              FROM table_name t
           ) s
     WHERE cnt = 1
    ;

Posting Permissions

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