To return only the latest row

Question

I have a table storing transaction called TRANSFER . I needed to write a query to return only the newest entry of transaction for the given stock tag (which is a unique key to identify the material) so i used the following query

SELECT a.TRANSFER_ID
     , a.TRANSFER_DATE
     , a.ASSET_CATEGORY_ID
     , a.ASSET_ID
     , a.TRANSFER_FROM_ID
     , a.TRANSFER_TO_ID
     , a.STOCK_TAG
 FROM TRANSFER a
INNER JOIN (
              SELECT STOCK_TAG
                   , MAX(TRANSFER_DATE) maxDATE
                FROM TRANSFER
               GROUP BY STOCK_TAG
            ) b
   ON a.STOCK_TAG = b.STOCK_TAG AND
      a.Transfer_Date =b.maxDATE

But i end with a problem where when more than one transfer happens on the same transfer date it returns all the row where as i need only the latest . how can i get the latest row?

edited:

transfer_id   transfer_date   asset_category_id  asset_id   stock_tag 
 1               24/12/2010      100               111         2000
 2               24/12/2011      100               111         2000

If two transfers happened on the same date, how do you expect to determine which one is the latest? (If `TRANSFER.TRANSFER_ID` is an incrementing field, you could select `MAX(TRANSFER_ID)` in the subquery instead, and then join on that value.) — cdhowie, Nov 27 '12 at 06:59
If you ever have a situation where records are not inserted in transfer_date order then this logic of joining on max transfer_id and transfer_id will fail. I wouldn't rely on it myself. — David Aldridge, Nov 27 '12 at 07:30

score 1 · Answer 1 · answered Nov 27 '12 at 07:04

1

Consider selecting MAX(TRANSFER_ID) in your subquery, assuming that TRANSFER_ID is an incrementing field, such that later transfers always have larger IDs than earlier transfers.

answered Nov 27 '12 at 07:04

cdhowie

158,093
24
286
300

though that solution worked . Is there any other way of doing it ? – Sam Nov 27 '12 at 07:11
Not from what I can see given your schema. Consider adjusting `TRANSFER_DATE` to be a datetime column. Then you will have second precision when looking for the latest order. – cdhowie Nov 27 '12 at 07:12
Well that will be something to think about thanks. – Sam Nov 27 '12 at 07:17

score 1 · Accepted Answer · answered Nov 27 '12 at 07:33

To avoid the potential situation of rows not being inserted in transfer_date order, and maybe for performance reasons, you might like to try:

 select
   TRANSFER_ID      ,
   TRANSFER_DATE    ,
   ASSET_CATEGORY_ID,
   ASSET_ID         ,
   TRANSFER_FROM_ID ,
   TRANSFER_TO_ID   ,
   STOCK_TAG
 from (
   SELECT
     TRANSFER_ID      ,
     TRANSFER_DATE    ,
     ASSET_CATEGORY_ID,
     ASSET_ID         ,
     TRANSFER_FROM_ID ,
     TRANSFER_TO_ID   ,
     STOCK_TAG        ,
     row_number() over (
       partition by stock_tag
       order by     transfer_date desc,
                    transfer_id desc) rn
   FROM TRANSFER)
 where rn = 1

To return only the latest row

2 Answers2