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?

score 1 · Answer 1 · answered Dec 06 '19 at 09:14

You can use the keep clause of aggregates to return the first/last value, ordered by another:

create table t (
  c1 date, c2 varchar2(10), c3 varchar2(10)
);
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

insert into t values ( '2019-05-27 08:37:07', '0242625', '00265929' );
insert into t values ( '2019-05-27 07:52:29', '0282971', '00265929' );
insert into t values ( '2019-05-27 07:52:29', '0282972', '00265929' );
insert into t values ( '2019-05-27 07:52:29', '0696864', '00265929' );
insert into t values ( '2018-02-13 22:57:09', '0282984', '00265929' );
insert into t values ( '2019-05-27 07:52:29', '0395347', '00265929' );
insert into t values ( '2019-05-27 07:52:29', '0242712', '00265929' );
insert into t values ( '2019-05-27 07:52:29', '0242624', '00265929' );
insert into t values ( '2019-05-27 07:52:29', '0441449', '00265929' );
insert into t values ( '2019-05-27 07:52:29', '0400026', '00265929' );

select max ( c1 ) c1,
       max ( c2 ) keep (
         dense_rank first
         order by c1 desc
       ) c2,
       c3
from   t
group  by c3;

C1                    C2        C3         
2019-05-27 08:37:07   0242625   00265929

Littlefoot · Accepted Answer · 2019-12-06T10:18:27.417

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;

Sure; it just represents your data. As I said - code you'd need begins at line #6. — Littlefoot, Dec 06 '19 at 09:30

Barbaros Özhan · Answer 3 · 2019-12-06T09:48:21.490

0

You can use MAX() OVER (PARTITION BY ...) analytic function :

WITH t AS
(   
 SELECT MAX(v.upd_dtime) OVER (PARTITION BY v.item_no) AS MaxT, 
        v.vo_no AS vo_no, v.item_no, v.upd_dtime 
   FROM vo_order_t v
  WHERE v.item_no = '00265929'
)
SELECT *
  FROM t
 WHERE upd_dtime = MaxT

If you're using version 12c+, then directly use FETCH FIRST 1 ROW ONLY to get the latest article :

 SELECT MAX(v.upd_dtime) OVER (PARTITION BY v.item_no) AS MaxT, 
        v.vo_no AS vo_no, v.item_no, v.upd_dtime 
   FROM vo_order_t v
  WHERE v.item_no = '00265929'
  FETCH FIRST 1 ROW ONLY

Demo

edited Dec 06 '19 at 09:48

answered Dec 06 '19 at 09:41

Barbaros Özhan

59,113
10
31
55

The item_t column don't exist in the vo_order_t table. It just exists in the vo_item_t. – Bryan Dec 06 '19 at 09:44
I couldn't understand what you meant by figuring out `item_t` @Bryan . There's already no such column. – Barbaros Özhan Dec 06 '19 at 10:24

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

3 Answers3