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.

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:
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.

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

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, i was talking about the "fetch first 3 rows only" solution, not your join

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

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.

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:

SQL0158N During Delete by ROW_NUMBER()

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

Bill

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.

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

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

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 ()

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

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.

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".