0

I have this data sample :

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)?

Thanks in advance.

Edited:

 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

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;

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

Wiz
  • 113
  • 12
  • 1
    The the last row of the table or per card or per service or per card and service? – Thorsten Kettner Mar 29 '18 at 10:39
  • Probably a duplicate of https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column – MT0 Mar 29 '18 at 10:44
  • 2
    As you can see from the answers, your request "most recent and higher value" is ambiguous. Most of us think you mean "most recent and in case of ties the higher value", some think you mean "highest value and in case of ties the most recent of these". – Thorsten Kettner Mar 29 '18 at 10:55
  • it's exactly that, the most recent and in case of ties the higher value... – Wiz Mar 29 '18 at 10:59

5 Answers5

3

So you want the row with the most recent date and highest value?

If you're on 12.1 and up, you can use fetch first. Sort by the date and value descending and get one row:

create table t (
  card int, service int, dt date, val int
);

insert into t values (1, 1, date'2014-10-27', 5);
insert into t values (1, 1, date'2014-10-28', 5);
insert into t values (1, 1, date'2014-10-28', 6);

select * from t
order  by dt desc, val desc
fetch first 1 row only;

CARD   SERVICE   DT                     VAL   
     1         1 28-OCT-2014 00:00:00       6

On 11.2 and earlier you need a subquery where you assign a row number sorted by date and value:

with ranked as (
  select t.*,
         row_number() over (order by dt desc, val desc) rn
  from   t
)
  select * from ranked
  where  rn = 1;

CARD   SERVICE   DT                     VAL   RN   
     1         1 28-OCT-2014 00:00:00       6    1
Chris Saxon
  • 9,105
  • 1
  • 26
  • 42
  • How can you do order by date column may chances of date is now stored in order manner – Jay Shankar Gupta Mar 29 '18 at 10:43
  • 1
    Hi Chris. Loved your quiz questions in PL/SQL challenge :) – Kaushik Nayak Mar 29 '18 at 10:45
  • @ChrisSaxon thanks for the reply. I already editted my first post. I want the row most recent and in case of ties the higher value :) – Wiz Mar 29 '18 at 11:01
  • @Wiz that's what those queries do - find the newest, then resolve ties by value – Chris Saxon Mar 29 '18 at 11:06
  • @ChrisSaxon Hello again Chris, thanks for your help. I probably explain wrong the situation, but this only will return one line at time. What I want is to return the most recent record and then in case of ties, returns the higher value by card and service (and not just one record), is it more clear now? Thanks for all the help. – Wiz Apr 05 '18 at 12:06
  • @Wiz I'm not clear what you're trying to do - what output do you expect given the data in the question? In which cases do you want more than one row in the results? – Chris Saxon Apr 05 '18 at 12:35
2

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;

Demo

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • @Wiz :U can ask that as a separate question. Editing a question after a long time since it's answered is considered rude in S.o. Please don't edit it. You may accept this answer or other if you found it useful as it answers the original question. – Kaushik Nayak Apr 10 '18 at 12:19
  • Ok. I'll do that. Thanks again. – Wiz Apr 10 '18 at 12:58
  • https://stackoverflow.com/questions/49755771/query-to-get-the-most-recent-record-and-in-case-of-ties-with-the-higher-value?noredirect=1#comment86526699_49755771. When you could take a look at it ;) – Wiz Apr 10 '18 at 14:43
1

Try this:

select *
from (
  select x.*
  from <tablename> x
  where date = (select max(date) from <tablename> )
  order by value desc
) where rownum<2 ;
MT0
  • 143,790
  • 11
  • 59
  • 117
SimpleOne
  • 98
  • 7
  • While this is possible in Oracle, it is not at all standard-compliant, because it relies on the order of a derived table (subquery). I would not encourage this and rather stick to the standard as shown in Chris Saxon's answer. – Thorsten Kettner Mar 29 '18 at 10:47
1

Try this query : -

    SELECT TOP 1 * FROM tableName ORDER BY dateCol1 DESC,valueCol2 DESC;
Ganesh Rana
  • 63
  • 9
0

Simple Solution in MySQL,

    select * from demo_table t
    where value = (select max(value) from demo_table)
    order by date desc limit 1
Ravi Panchal
  • 7
  • 4