如何选择两列中具有最大值的记录? [英] How to select records with maximum values in two columns?

问题描述

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

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

这篇关于如何选择两列中具有最大值的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！