Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Dec 2007
    Posts
    11

    Unanswered: Need help DB2 - returning last three records for each item

    Hi Guys,

    Forgive me with this trivial question but so far couldn't find an answer. I am trying to write an sql query that will return the last three entries for each item in a warehouse. I realised that I need some sort of control structure to do this could anyone give me some pointers?

    I am using ODBC to connect to a remote DB@ database on an AS400. Don't know which version / mod etc. Below is a copy of my code.

    Thanks.


    select ITEM, USER, DATE, ENTNUM, RCPT, TQTY, STATUS
    from DETLP
    where WHSE = 'R' and TTYP = 'R' and ITEM in
    (select distinct sitem
    from ITEMFILE
    where STATUS <> 'i') //i.e. item is active
    order by SDATE desc
    fetch first 3 rows only

    -----

    This query returns only three records, which happen to be of the last three items.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by solaris_bb
    order by SDATE desc
    fetch first 3 rows only
    This is indeed the DB2 way to obtain the 3 "most recent" entries (assuming sdate is a DATE column).

    A generic way to achieve this would be with a self-join and a group by;
    I'm now simplifying the problem to a table t with columns sdate and item:
    Code:
    SELECT   t1.item, t1.sdate
    FROM     t AS t1 LEFT OUTER JOIN t AS t2 ON t1.sdate < t2.sdate
    GROUP BY t1.item, t1.sdate
    HAVING   count(t2.item) < 3
    Note that you will in general obtain more than 3 rows, since there might be "ties", i.e., rows with equal sdate.

    The "fetch first 3 rows" or equivalent in other RDBMS implementations is of course much more performant, but less correct since in case of ties, not all of them are shown.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Peter.Vanroose
    This is indeed the DB2 way to obtain the 3 "most recent" entries (assuming sdate is a DATE column).
    but it is not the solution to the stated problem: last three per item

    i'm sure there's also an ANSI SQL solution involving RANK() and OVER, but who knows if it'll work in DB2

    i think we should move this thread to the DB2 forum instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by r937
    but it is not the solution to the stated problem: last three per item
    Sure, but with a little variation on my suggestion, viz. adding an extra column "col3" to the "group by" and and extra "t1.col3 = t2.col3" to the join condition, you'll get the required "per col3".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    peter, i was talking about the "fetch first 3 rows only" solution, not your join

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

  6. #6
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    use analytic function row_number
    select * from (
    select ITEM, USER, DATE, ENTNUM, RCPT, TQTY, STATUS, row_number() over (partition by ... order by ..... ) as row_order
    from DETLP
    where WHSE = 'R' and TTYP = 'R' and ITEM in
    (select distinct sitem
    from ITEMFILE
    where STATUS <> 'i') //i.e. item is active
    ) where row_order <=3
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by r937
    peter, i was talking about the "fetch first 3 rows only" solution, not your join
    Sure, I know

    The last version of DB2 allows for a "FETCH FIRST 3 ROWS ONLY" inside a subquery, which could be combined with the original query (without "FETCH FIRST") as extra WHERE condition (either with IN or with EXISTS).
    But the "correct" way of doing this is by using one of the OLAP functions (see e.g. baloo99's solution), also available in the last version of DB2.
    --_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
    Jul 2007
    Posts
    16
    Solaris,

    I don't know if this will help, but with the aid of Sathyaram I was able to solve a similar problem using row_number. The thread below contains a working sample of a simple CLP using the row_number function.

    Sath also suggested that I look at the rank and dense_rank functions (which, btw Sath, I began to examine today...). Here are the links to my thread and the DB2 searchable Information Center:

    http://www.dbforums.com/showthread.php?t=1625506

    http://publib.boulder.ibm.com/infoce...w/v8/index.jsp

    Bill

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by caleysoldman
    ... rank and dense_rank functions ...
    These are the functions which will "solve" the "ties" problem which I was referring to in my previous post: using one of these instead of row_number() will possibly show more than 3 rows per group, so avoid the "random" choice of three.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Jul 2007
    Posts
    16
    You might be able to adapt this sql (note that I reversed the sort order at the end of the statement.

    SELECT USER_SID, USER_LOGINS FROM (
    SELECT
    ROW_NUMBER() OVER(PARTITION BY USER_SID ORDER BY USER_LOGINS_SID DESC) AS ROWNUM,
    USER_SID,
    USER_LOGINS_SID
    FROM
    BILL.ORG_USER_LOGINS
    WHERE
    USER_SID
    IN (
    SELECT DISTINCT
    LOGS.USER_SID
    FROM
    ORGANIZATION ORG,
    USER_SID USER,
    USER_LOGINS LOGS
    WHERE
    ORG.ORG_SID=USER.ORG_SID
    AND
    USER.USER_SID=LOGS.USER_SID
    AND
    ORG.ORG_SID = ?)
    ) AS WHATEVER_UNUSED_WORD_YOU_LIKE
    WHERE
    ROWNUM <= 3
    ORDER BY
    USER_SID,ORG_LOGINS_SID
    ;

    Hope you're having as much fun as me...

    Bill

  11. #11
    Join Date
    Dec 2007
    Posts
    11

    Returning 3 rows for each item

    Hi Guys,

    Thanks alot for your responses and sorry for the tardy response - usually knee deep this time of year. I modded your script but now I am getting the error:

    >[Error] Script lines: 1-14 -------------------------
    [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token ( was not valid. Valid tokens: , FROM INTO. 

    Here is a copy of my script:

    SELECT ITEM, USER, TDAT, TENT, RCPT, TQTY, CSTU AS UNITCOST, DTCSTS AS STANDCOST
    FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY ITEM ORDER BY TENT DESC) AS ROWNUM,
    ITEM, USER, TDAT, TENT, RCPT, TQTY, CSTU, CSTS
    FROM TEST.DETLP
    WHERE RCPT = '1' AND WHSI = 'R' AND TTYP = 'R' AND ITEM IN
    ( SELECT DISTINCT SITEM
    FROM TEST.ITEMP
    WHERE SSTTS <> 'I' //I.E. ITEM IS ACTIVE
    )
    )
    AS WHATEVER_UNUSED_WORD_I_LIKE
    WHERE ROWNUM <= 3
    ORDER BY ITEM ASC, TDAT DESC;


    I wish I was having fun... lol


    Quote Originally Posted by caleysoldman
    You might be able to adapt this sql (note that I reversed the sort order at the end of the statement.
    .
    .
    .
    Hope you're having as much fun as me...

    Bill

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by solaris_bb
    [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token ( was not valid. Valid tokens: , FROM INTO.

    Here is a copy of my script:

    SELECT ITEM, USER, TDAT, TENT, RCPT, TQTY, CSTU AS UNITCOST, DTCSTS AS STANDCOST
    FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY ITEM ORDER BY TENT DESC) AS ROWNUM,
    ITEM, USER, TDAT, TENT, RCPT, TQTY, CSTU, CSTS
    FROM TEST.DETLP
    WHERE RCPT = '1' AND WHSI = 'R' AND TTYP = 'R' AND ITEM IN
    ( SELECT DISTINCT SITEM
    FROM TEST.ITEMP
    WHERE SSTTS <> 'I' //I.E. ITEM IS ACTIVE
    )
    )
    AS WHATEVER_UNUSED_WORD_I_LIKE
    WHERE ROWNUM <= 3
    ORDER BY ITEM ASC, TDAT DESC;
    Apparently your version of DB2 does not yet support row_number()
    since it's the "(" of "row_number()" it's complaining about.

    Only fallback then: do the self-join, group by primary key of first instance, thereby generating a "count()" column with the same content as the row_number() or better: rank() column, and use that result in your outer FROM ()
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    btw
    db2 9.5 has now rownum also, :-)
    Set the DB2 registry to support an application using DUAL and ROWNUM queries.
    db2set DB2_COMPATIBILITY_VECTOR=3
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  14. #14
    Join Date
    Dec 2007
    Posts
    11
    Hi Rahul,

    Sounds nifty as I would not have to change my code, unfortunately that database and server are out of my jurisdiction so I doubt I'd be able to execute that change.

    thanks.

    Quote Originally Posted by rahul_s80
    btw
    db2 9.5 has now rownum also, :-)
    Set the DB2 registry to support an application using DUAL and ROWNUM queries.
    db2set DB2_COMPATIBILITY_VECTOR=3

  15. #15
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Did you try to write and run the "join & group by & count" alternative to the rank() solution?
    I'd be happy to help getting it right if you have a "first attempt".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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