CODEWITHSUNDEEP
sqloracle-database
Wiz
Wiz

Reputation: 113

Query to get the most recent record and with the higher value

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

Upvotes: 0

Views: 1041

Answers (5)

Ravi Panchal
Ravi Panchal

Reputation: 5

Simple Solution in MySQL,

    select * from demo_table t
    where value = (select max(value) from demo_table)
    order by date desc limit 1

Upvotes: 0

Ganesh Rana
Ganesh Rana

Reputation: 63

Try this query : -

    SELECT TOP 1 * FROM tableName ORDER BY dateCol1 DESC,valueCol2 DESC;

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

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

Upvotes: 2

SimpleOne
SimpleOne

Reputation: 98

Try this:

select *
from (
  select x.*
  from <tablename> x
  where date = (select max(date) from <tablename> )
  order by value desc
) where rownum<2 ;

Upvotes: 1

Chris Saxon
Chris Saxon

Reputation: 9875

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

Upvotes: 3

Related Questions