如何在两列中选择最大值的记录? [英] 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

所以我使用了查询(顺便说一句，在这种情况下这是正确的方法吗?):

So I used the query (btw, is it the right way in such situation?):

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表中添加一个新的"quarter"列.并按年份和季度选择最新记录.

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

我只是无法想象可以做到的查询，希望您能为我提供帮助.

I just can't imagine the query that can do it, and hope you'll help me with it.

推荐答案

分析功能是您的朋友:

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屋！