Query to get the most recent record and (in case of ties) with the higher value or percentage by card and service

Question

I started the question here ( Query to get the most recent record and with the higher value) but 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;

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

check out this issue https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns — jasinth premkumar, Apr 10 '18 at 14:12
and also this https://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression — jasinth premkumar, Apr 10 '18 at 14:13
Thanks for the reply, but the links only indicates how to user the group by. — Wiz, Apr 10 '18 at 14:20
So, if they could be 'P' or 'V' both for a given group, what criteria do u use to decide which one is to be considered for a `card,service` ? — Kaushik Nayak, Apr 10 '18 at 15:26
@KaushikNayak For the same pair, it will be P or V. I would never have 2 records for the same pair with a P and other with V. — Wiz, Apr 10 '18 at 16:03

score 0 · Accepted Answer · answered Apr 10 '18 at 15:16

You have not mentioned whether the column type varies for a given card,service pair. Assuming it is same, you should be able to get the result with a nested select, including type in the inner select and group by .

 SELECT card
    ,service
    ,CASE 
        WHEN type = 'v'
            THEN value
        ELSE perc
        END AS max_result
FROM (
    SELECT card
        ,service
        ,type
        ,MAX(date_t) AS Date_t
        ,MAX(value) KEEP (
            DENSE_RANK FIRST ORDER BY date_t DESC
            ) AS value
        ,MAX(percentage) KEEP (
            DENSE_RANK FIRST ORDER BY date_t DESC
            ) AS perc
    FROM yourtable
    GROUP BY card
        ,service
        ,type
    );

Demo

Query to get the most recent record and (in case of ties) with the higher value or percentage by card and service

1 Answers1

Linked