如何选择两列中具有最大值的记录?

2021-12-06 00:00:00 sql oracle

很难为这个问题想出一个可以理解的标题.我会试着用一个例子来解释.

It was hard to come up with an understandable title for this question. I'll try to explain with an example.

首先我在 Oracle DB 中有一个简单的表 INFO:

First I had a simple table INFO in Oracle DB:

year      type      message
----      ----      -------
2001      1         cakes are yammy
2003      2         apples are dangerous
2012      2         bananas are suspicious
2005      3         cats are tricky

我需要选择某些类型的最新消息(例如 type = 1 或 type = 2):

And I need to select newest messages of certain types (for example type = 1 or type = 2):

2001      1         cakes are yammy
2012      2         bananas are suspicious

所以我使用了查询:

select * from INFO i 
where year = (select max(year) from INFO i_last where i.type = i_last.type) 
and i.type in (1, 2)

但现在我需要添加一个新的季度"列到我的 INFO 表.并按年份和季度选择最新记录.

But now I need to add a new "quarter" column to my INFO table. And select the newest records by year and quarter.

year      quarter      type      message
----      -------      ----      -------
2001      2            1         cakes are yammy
2012      3            2         onions are cruel
2012      1            2         bananas are suspicious
2005      1            3         cats are tricky

类型 1 或 2 的最新记录将是:

The newest records with type 1 or 2 will be:

2001      2            1         cakes are yammy
2012      3            2         onions are cruel

这样的查询应该是什么样的?

How should such query look like?

推荐答案

分析函数是你的朋友:

SELECT   MAX( year    ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS year,
         MAX( quarter ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS quarter,
         MAX( message ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS message,
         type
FROM     info
GROUP BY type;

SQLFIDDLE

相关文章