tableABC有独立的日期和时间列:
+-----------+--------+-----------+-----------+
| AccountID | userID | date | timestamp |
+-----------+--------+-----------+-----------+
| 123 | 1 | 29-MAR-13 | 21005 |
| 123 | 1 | 29-MAR-13 | 11005 |
| 123 | 1 | 23-MAR-13 | 21005 |
+-----------+--------+-----------+-----------+
我需要日期列和时间戳列的最大值。我编写的查询只执行日期的max,而不执行时间戳:
select *
from tableABC rn
where userID = '1'
and accountID = '123'
and date =
(
select max(date) MaxDate
from tableABC b
where b.userID = rn.userID
and b.accountID = rn.accountID
);
首先,Oracle不允许您将列命名为DATE
,除非您将列名用双引号括起来,但我建议您不要将其命名为DATE
-您将避免各种麻烦。
Oracle不介意一个列被命名为TIMESTAMP
,但这仍然是一个"特殊"的Oracle词,所以我再次建议不要这样做。Oracle不会感到困惑,但是任何读你代码的人都会感到困惑。
假设列命名为my_date
和my_timestamp
。要查询所有用户的顶部日期/时间戳,请执行如下操作:
SELECT * FROM (
SELECT
AccountID,
userID,
my_date,
my_timestamp,
ROW_NUMBER() OVER (
PARTITION BY AccountID, UserID
ORDER BY my_date DESC, my_timestamp DESC) AS DateTimestampRank
FROM TableABC
) WHERE DateTimeStampRank = 1
如果您只想查询一个用户,那么可以使用@GordonLinoff的答案。
使用order by
:
select *
from (select *
from tableABC
order by "date" desc, "timestamp" desc
) t
where rownum = 1
如果您试图获得每个accountID/userID组合的最大值,则使用row_number()
:
select *
from (select *, row_number() over (partition by userId, accountId order by "date" desc, "timestamp" desc) as seqnum
from tableABC
) t
where seqnum = 1
你为什么不先按日期排序,然后按时间排序,然后排在最上面一排呢?
SELECT ACCOUNTID,USERID,MAX(DATE1),MAX(TIMESTAMP) FROM tableABC
GROUP BY ACCOUNTID,USERID,DATE1;