的Oracle SQL：选择单行多列之间的最晚日期

本文介绍了的Oracle SQL：选择单行多列之间的最晚日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的基本表的结构是这样的：

What I want to do is select the id of the row with the highest update_date or create_date. So something like this (although this doesn't work - pID is defined as an out variable in a stored procedure):

This would return a single id of essentially the last row that was modified in the table. Little help?

P.S. This is in Oracle11g. I'm currently using MERGE to upsert rows into tables, and using this method to get the id of the last upserted row. If you have any constructive criticism of this (potentially with an alternative solution), I'm all ears. I've read plenty of arguments going both ways as to why this is good / bad. I'm going to call this procedure in c#, so any other tips there is appreciated.

解决方案

I think the structure you want is this:

select id
from (SELECT ID
      FROM EXAMPLE_TABLE
      order by GREATEST(CREATE_DATE, UPDATE_DATE) desc
     )
where rownum = 1

However, I suppose it is possible for one of the dates to be NULL. If so:

select id
from (SELECT ID
      FROM EXAMPLE_TABLE
      order by coalesce(GREATEST(CREATE_DATE, UPDATE_DATE), CREATE_DATE) desc
     )
where rownum = 1

These queries are ordering by the larger of the two values on each row, and then selecting the maximum value.

这篇关于的Oracle SQL：选择单行多列之间的最晚日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！