Difficult query (DB2)

Question

Suppose I have a table called spitems with the following fields:

• spitemid (unique key)
• modifiedon (timestamp)
• parentid
• a number of other unsignificant fields

What I want to retrieve, is the spitem rows with the highest modifiedon day for each parentid.

However, be aware that the modifiedon timestamp is not unique, so it is possible that for one parent id, there are two spitemids with the same modifiedon timestamp. In that case, I need one of these two spitemids listed, I don't care which one.

So to be clear: the list I return should contain all the parentids once and only once.

Answer 1

Based on your requirements, following should get you the latest items.

SELECT    t1.*
FROM      Table t1
          INNER JOIN (
            SELECT    spitemid = MAX(t1.spitemid)
            FROM      Table t1
                      INNER JOIN (
                        SELECT   parentid, modifiedon = MAX(modifiedon)
                        FROM     Table
                        GROUP BY parentid
                      ) t2 ON t2.parentid = t1.parentid
                              AND t2.modifiedon = t1.modifiedon
            GROUP BY  t1.parentid, t1.modifiedon
          ) t2 ON t2.spitemid = t1.spitemid

Answer 2

update

meeting over, here is my shot:

select * 
from table
join where spitmid in 
  (select max(spitmid) 
   from table
   join 
     (select parentid, max(modifiedon) as d from table group by parentid) inlist
      on table.parentid = inlist.parentid and table.modifiedon = inlist.d
   group by parentid, datemodified
  ) 

old entry

not sure if this is different on DB2, here it is for sql server.

    select * 
    from table
    join (select parentid, max(modifiedon) as d from table group by parentid) as toplist on 
      table.parentid = toplist.parentid and table.modifiedon = toplist.d

hmm... this will return more than one for the dups... can't fix it now, have to go to a meeting.

Answer 3

You can do it with two nested subqueries. The first gets max modifiedon for each parentid, and then the second gets max spitemid for each parentid/modifiedon group.

SELECT *
FROM spitems
WHERE spitemid IN 
(
    SELECT      parentid, modifiedon, max(spitemid) spitemid
    FROM (
        SELECT      parentid, MAX(modifiedon) modifiedon
        FROM        spitems
        GROUP BY    parentid
    ) A
    GROUP BY parentid, modifiedon
)

Answer 4

SELECT sr.receiving_id, sc.collection_id FROM stock_collection as sc, stock_requisation as srq, stock_receiving as sr WHERE (sc.stock_id = '" & strStockID & "' AND sc.datemm_issued = '" & strMM & "' AND sc.qty_issued >= 0 AND sc.collection_id = srq.requisition_id AND srq.active_status = 'Active') OR (sr.stock_id = '" & strStockID & "' AND sr.datemm_received = '" & strMM & "' AND sr.qty_received >= 0)

Answer 5

A common table expression will give you the opportunity to number the rows before you issue the final SELECT.

WITH items AS 
(
SELECT spitemid, parentid, modifiedon, 
ROWNUMBER() OVER (PARTITION BY parentid ORDER BY modifiedon DESC) AS rnum
FROM yourTable
)
SELECT spitemid, parentid, modifiedon FROM items WHERE rnum = 1
;