I started the question here ( <a href="https://stackoverflow.com/questions/49553679/query-to-get-the-most-recent-record-and-with-the-higher-value" rel="nofollow">Query to get the most recent record and with the higher value</a>) 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.Answer1:
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
type in the inner
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 );
<a href="http://sqlfiddle.com/#!4/1b0cd/2" rel="nofollow"><strong>Demo</strong></a>