Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Unanswered: "Distinct" queries

    Friends,
    I am facing an issue in my queries.
    Query 1 and 2 is expected to give the same results. But "Distinct" in Query 2 is not working and it results in exactly twice the rows of result given by Query 1.
    How to resolve it?
    I am giving both the queries here.
    Query 1:
    SELECT DISTINCT A.NUM_PART, A.AMT_ECR_PART_RETL, A.TXT_ECR_PART_COM, B.DES_ECR_VEMSS_EXT
    FROM ECRUSER.ECR_MODL_YR_PART A, ECRUSER.ECR_VEMSS B, ECRUSER.ECR_MODL_VEMSS_PART C
    WHERE A.NUM_PART = C.NUM_PART AND B.CDE_ECR_VEMSS = C.CDE_ECR_VEMSS AND
    A.DTE_MODL_YR = B.DTE_MODL_YR AND B.DTE_MODL_YR = C.DTE_MODL_YR AND A.DTE_MODL_YR = C.DTE_MODL_YR


    Resultset 1: It consists of 13 rows ( required resultset )

    Query 2:

    SELECT * FROM (SELECT DISTINCT A.NUM_PART, A.AMT_ECR_PART_RETL, A.TXT_ECR_PART_COM, B.DES_ECR_VEMSS_EXT, ROWNUMBER() OVER (ORDER BY A.DTE_MODL_YR DESC) AS RN FROM ECRUSER.ECR_MODL_YR_PART A, ECRUSER.ECR_VEMSS B, ECRUSER.ECR_MODL_VEMSS_PART C WHERE A.DTE_MODL_YR = B.DTE_MODL_YR AND B.DTE_MODL_YR = C.DTE_MODL_YR AND A.DTE_MODL_YR = C.DTE_MODL_YR AND A.DTE_MODL_YR BETWEEN '1900-01-01' AND '2099-01-01' AND A.NUM_PART = C.NUM_PART AND B.CDE_ECR_VEMSS = C.CDE_ECR_VEMSS AND A.NUM_PART BETWEEN '' AND 'ZZZZZZZZZZZZZZZZZZZZZZ') AS RES WHERE RN between 0 and 30;

    Resultset 2: It consists of 26 rows ( with duplicates )

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Select distinct will return only unique rows. This means the entire row. If any part is different, then it will be included. You have row_number() in your query, so every row will be different.

    Andy

  3. #3
    Join Date
    Apr 2007
    Posts
    63
    I have no requirement to display the rownumber. I am using it only to select a particular range of values ( rows between 20 and 30 etc)
    So, is it possible to retrieve only distinct rows?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What happens is this: You use the OLAP function ROWNUMBER() that produces a distinct number for each row. If you apply DISTINCT on that, there will not by any duplicates (due to the different row numbers) so that nothing gets removed.

    What you have to do is to do the DISTINCT first, then filter based on the row number.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    What about
    Code:
    SELECT DISTINCT A.NUM_PART, A.AMT_ECR_PART_RETL, A.TXT_ECR_PART_COM, B.DES_ECR_VEMSS_EXT
    FROM ECRUSER.ECR_MODL_YR_PART A, ECRUSER.ECR_VEMSS B, ECRUSER.ECR_MODL_VEMSS_PART C
    WHERE   A.DTE_MODL_YR = B.DTE_MODL_YR
      AND   B.DTE_MODL_YR  = C.DTE_MODL_YR
      AND   A.DTE_MODL_YR = C.DTE_MODL_YR
      AND   A.DTE_MODL_YR BETWEEN '1900-01-01' AND '2099-01-01'
      AND   A.NUM_PART = C.NUM_PART
      AND   B.CDE_ECR_VEMSS = C.CDE_ECR_VEMSS
      AND   A.NUM_PART BETWEEN '' AND 'ZZZZZZZZZZZZZZZZZZZZZZ'
      AND  ROWNUMBER() OVER (ORDER BY A.DTE_MODL_YR DESC) between  0 and 30;
    That is, just remove the column causing the duplicates to be non-duplicate, viz. the rownumber() column.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Apr 2007
    Posts
    63

    Thumbs up

    The above code gives

    SQL0120N Invalid use of an aggregate function or OLAP function.

    Anyways Thank you so much for reply.

    I tried the below query. Its working fine, retrieving the required resultset without duplicates and it enables searches within ranges ( 1 to 30 , 5 to 10 rows etc) also:

    select * from (select dte_modl_yr,num_part, amt_ecr_part_retl,TXT_ECR_PART_COM,DES_ECR_VEMSS_E XT, rownumber() over (order by dte_modl_yr desc) as rn from (SELECT distinct A.NUM_PART, a.dte_modl_yr, A.AMT_ECR_PART_RETL, TXT_ECR_PART_COM, B.DES_ECR_VEMSS_EXT FROM ECRUSER.ECR_MODL_YR_PART A, ECRUSER.ECR_VEMSS B, ECRUSER.ECR_MODL_VEMSS_PART C WHERE A.DTE_MODL_YR = B.DTE_MODL_YR AND B.DTE_MODL_YR = C.DTE_MODL_YR AND A.DTE_MODL_YR = C.DTE_MODL_YR AND A.DTE_MODL_YR BETWEEN '1900-01-01' AND '2099-01-01' AND A.NUM_PART = C.NUM_PART AND B.CDE_ECR_VEMSS = C.CDE_ECR_VEMSS AND A.NUM_PART BETWEEN '' AND 'ZZZZZZZZZZZZZZZZZZZZZZ')as res) as res1 where AMT_ECR_PART_RETL IS NULL AND rn between 0 and 30;

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    What about
    Code:
    SELECT DISTINCT *
    FROM   (SELECT NUM_PART, AMT_ECR_PART_RETL, TXT_ECR_PART_COM, DES_ECR_VEMSS_EXT
            FROM
             (SELECT ROWNUMBER() OVER (ORDER BY A.DTE_MODL_YR DESC) AS rn,
                     A.NUM_PART, A.AMT_ECR_PART_RETL, A.TXT_ECR_PART_COM, B.DES_ECR_VEMSS_EXT
              FROM   ECRUSER.ECR_MODL_YR_PART A, ECRUSER.ECR_VEMSS B, ECRUSER.ECR_MODL_VEMSS_PART C
             WHERE   A.DTE_MODL_YR = B.DTE_MODL_YR
               AND   B.DTE_MODL_YR  = C.DTE_MODL_YR
               AND   A.DTE_MODL_YR = C.DTE_MODL_YR
               AND   A.DTE_MODL_YR BETWEEN '1900-01-01' AND '2099-01-01'
               AND   A.NUM_PART = C.NUM_PART
               AND   B.CDE_ECR_VEMSS = C.CDE_ECR_VEMSS
               AND   A.NUM_PART BETWEEN '' AND 'ZZZZZZZZZZZZZZZZZZZZZZ') x
            WHERE rn between  0 and 30) y
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Apr 2007
    Posts
    63

    Cool

    Great! Its working good with the required distinct result set. Thanks a lot

Posting Permissions

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