时间戳

首页 > 解决方案 > 返回最新日期的文章，sql max(DATE)

sql - 返回最新日期的文章，sql max(DATE)

问题描述

我有以下查询：

 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

此查询的输出如下：

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

但我希望输出如下：

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

如何修改我的查询以实现这一目标？

标签： sqloraclegreatest-n-per-grouptop-n

解决方案

您可以使用聚合的 keep 子句返回第一个/最后一个值，由另一个排序：

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

推荐阅读