查询以获取最新记录并获得更高的值 [英] Query to get the most recent record and with the higher value

问题描述

我有这个数据样本:

card  service       date       value
  1      1       27-10-2014      5
  1      1       28-10-2014      5
  1      1       28-10-2014      6

什么是返回最后一行(最近的，如果是平局，则返回更高的值)的最佳方法?

What is the best approach to return the last row (most recent and in case of ties the higher value)?

先谢谢了.

 card  service       date       value
   1      1       27-10-2014      5
   1      1       28-10-2014      5
   1      1       28-10-2014      6
   2      2       29-10-2014      7

这应该已经返回了第3条和第4条记录.

This should have returned the 3rd and 4th record.

感谢所有答复.但是今天我有一个小的更改请求.我将在百分比"列和字符"列中分别指出值或百分比.

Thanks for all the replies. But today I have a small change request. I will have a column with Percentage and another column with a Char to indicate if is a value or a percentage.

我正在尝试做这样的事情:

I am trying to do something like this:

 select  card,
                         service,
                         max(date),
                         case when type = 'v'
                         then
                         MAX(value) KEEP (
                            dense_rank first order by date desc
                        )
                         else 
                         max(percentage) valor keep (
                           dense_rank first order by date desc
                         ) end   
                 from table
                 group by card,
                 service;

但是我得到的是ORA-00979:不是GROUP BY表达式

But I am getting ORA-00979: not a GROUP BY expression

推荐答案

一种好方法是使用KEEP..DENSE_RANK或FIRST聚合函数.

One good way is to use KEEP..DENSE_RANK or FIRST aggregate function.

SELECT card
    ,service
    ,MAX(date_t)
    ,MAX(value) KEEP (
        DENSE_RANK FIRST ORDER BY date_t DESC
        ) AS value
FROM yourtable
GROUP BY card
    ,service;

演示

这篇关于查询以获取最新记录并获得更高的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！