Return the article with the latest date, sql max(DATE)

Question

I have the following query:

 SELECT  MAX(b.upd_dtime) as MaxT, b.vo_no as vo_no, y.item_no
 FROM vo_order_t b JOIN (
     SELECT a.vo_no, a.item_no FROM vo_item_t a where a.item_no IN('00265929')) y ON y.vo_no = b.vo_no
 GROUP BY b.vo_no, y.item_no

The output from this query is the following:

Date                Vo_No   Item_No
2019-05-27 08:37:07 0242625 00265929
2019-05-27 07:52:29 0282971 00265929
2019-05-27 07:52:29 0282972 00265929
2019-05-27 07:52:29 0696864 00265929
2018-02-13 22:57:09 0282984 00265929
2019-05-27 07:52:29 0395347 00265929
2019-05-27 07:52:29 0242712 00265929
2019-05-27 07:52:29 0242624 00265929
2019-05-27 07:52:29 0441449 00265929
2019-05-27 07:52:29 0400026 00265929

But I want the output to be the following:

Date                Vo_no   Item_No
2019-05-27 08:37:07 0242625 00265929

How can I modify my query to achieve that?

Answer 1

Analytic functions might help. Code you need begins at line #6.

SQL> with test (upd_dtime, vo_no, item_no) as
  2    (select 20190527, 242625, 265929 from dual union all
  3     select 20190213, 282984, 265929 from dual union all
  4     select 20190118, 400026, 265929 from dual
  5    )
  6  select upd_dtime, vo_no, item_no
  7  from (
  8         select upd_dtime, vo_no, item_no,
  9           row_number() over (partition by item_no order by upd_dtime desc) rn
 10         from test
 11       )
 12  where rn = 1;

 UPD_DTIME      VO_NO    ITEM_NO
---------- ---------- ----------
  20190527     242625     265929

SQL>

---

[EDIT: applied to your tables]

As you were unable to adjust the above code to your real tables (next time, it would be a good idea to post test case which includes CREATE TABLE and INSERT INTO sample data), here's how it might look like:

• yourq CTE represents query you posted. I don't know why you used an inline view (its alias was y); why didn't you simply join vo_order_t and vo_item_t, as I did?
• the rest is exactly the same as I posted it previously

So: copy/paste this code and execute it in your schema. If I didn't make any typo, it should be OK. If not, as I said - post test case.

with 
yourq as
-- your query, rewritten
  (select b.upd_dtime, b.vo_no, a.item_no
   from vo_order_t b join vo_item_t a on a.vo_no = b.vo_no
   where a.item_no = '00265929'
  )
select upd_dtime, vo_no, item_no
from (
      select upd_dtime, vo_no, item_no,
             row_number() over (partition by item_no order by upd_dtime desc) rn
      from yourq
     )
where rn = 1;