如何编写sql查询以选择一行中具有最大值的行 [英] how to write sql query to select rows with max value in one column

问题描述

我的桌子看起来像这样.

My Table looks like this.

Id   |  Name  |  Ref  | Date        | From
10   |  Ant   |  100  | 2017-02-02  | David
10   |  Ant   |  300  | 2016-01-01  | David
2    |  Cat   |  90   | 2017-09-09  | David
2    |  Cat   |  500  | 2016-02-03  | David
3    |  Bird  |  150  | 2017-06-28  | David

这是我想要的结果.

Id   |  Name  |  Ref  | Date       | From
3    |  Bird  |  150  | 2017-06-28 | David
2    |  Cat   |  500  | 2016-02-03 | David
10   |  Ant   |  300  | 2016-01-01 | David

我的目标是每个Id的最高引用，按订购日期的顺序排序.

My target is the highest Ref per Id, ordered by Order Date desc.

请告诉我有关如何使用pl/sql编写sql查询的信息.

Could you please tell me about how to write a sql query using pl/sql.

推荐答案

这种要求(您需要以一列为单位的最大或最小值，以另一列为分组，但需要最大或最小值行中的所有数据) 分析功能的用途差不多.我使用了row_number-如果可能建立联系，则需要澄清分配(请参见问题下方的我的评论)，然后根据具体情况，另一个分析函数可能更合适-也许是rank().

This kind of requirement (where you need the max or min by one column, grouped by another, but you need all the data from the max or min row) is pretty much what analytic functions are for. I used row_number - if ties are possible, you need to clarify the assignment (see my Comment under your question), and depending on the details, another analytic function may be more appropriate - perhaps rank().

with
     my_table ( id, name, ref, dt, frm ) as (
       select 10, 'Ant' , 100, date '2017-02-02', 'David' from dual union all
       select 10, 'Ant' , 300, date '2016-01-01', 'David' from dual union all
       select  2, 'Cat' ,  90, date '2017-09-09', 'David' from dual union all
       select  2, 'Cat' , 500, date '2016-02-03', 'David' from dual union all
       select  3, 'Bird', 150, date '2017-06-28', 'David' from dual
     )
-- End of simulated table (for testing purposes only, not part of the solution).
-- SQL query begins BELOW THIS LINE.
select   id, name, ref, dt, frm
from     (
           select id, name, ref, dt, frm,
                  row_number() over (partition by id order by ref desc, dt desc) as rn
           from   my_table
         )
where    rn = 1
order by dt desc
;

ID  NAME  REF  DT          FRM 
--  ----  ---  ----------  -----
 3  Bird  150  2017-06-28  David
 2  Cat   500  2016-02-03  David
10  Ant   300  2016-01-01  David

这篇关于如何编写sql查询以选择一行中具有最大值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！