CODEWITHSUNDEEP
sqloracle-database
Naminee
Naminee

Reputation: 167

SQL get max value with date smaller date

I have a table like this:

| colA | date | num |
|  x   | 1.7. |  2  |
|  x   | 3.7. |  1  |
|  x   | 4.7. |  3  |
|  z   | 1.8. |  0  |              (edit)
|  z   | 2.8. |  1  |
|  z   | 5.8. |  2  |

And I want a result like this:

| colA | date | maxNum |
|  x   | 1.7. |  null  |
|  x   | 3.7. |  2     |
|  x   | 4.7. |  2     |
|  z   | 1.8. |  null  |              (edit)
|  z   | 2.8. |  0     |
|  z   | 5.8. |  1     |

So I want to have the max(num) for every row where the date is smaller the date grouped by colA. Is this somehow possible with a simple query? It would be part of a bigger query needed for some calculations on big databases.

Edit: maxNum should be null if there is no value before a date in the group

Thanks in advance.

Upvotes: 1

Views: 58

Answers (3)

Matthew McPeak
Matthew McPeak

Reputation: 17944

Use MAX..KEEP syntax. 

select cola, 
       adate, 
       max(num) keep ( dense_rank first order by adate ) over (partition by cola ) maxnum,
       case when adate = min(adate) over ( partition by cola ) 
            then null 
            else max(num) keep ( dense_rank first order by adate ) over (partition by cola ) end maxnum_op
from   input;

+------+-------+--------+-----------+
| COLA | ADATE | MAXNUM | MAXNUM_OP |
+------+-------+--------+-----------+
| x    |   1.7 |      2 |           |
| x    |   3.7 |      2 |         2 |
| x    |   4.7 |      2 |         2 |
| z    |   2.8 |      1 |           |
| z    |   5.8 |      1 |         1 |
+------+-------+--------+-----------+

The MAXNUM_OP column shows the results you wanted, but you never explained why some of the values were supposed to be null. The MAXNUM column shows the results that I think you described in the text of your post.

Upvotes: 3

Popeye
Popeye

Reputation: 35930

You can use first_value and row_number analytical function as following:

Select cola,
       date, 
       case when row_number() over (partition by cola order by date) > 1 then
             first_value(num) over (partition by cola order by date) 
       end as maxnum
From your_table;

Cheers!!

Upvotes: 2

sticky bit
sticky bit

Reputation: 37507

One way is to use a subquery.

SELECT t1.cola,
       t1.date,
       (SELECT max(t2.num)
               FROM elbat t2
               WHERE t2.cola = t1.cola
                     AND t2.date < t1.date) maxnum
       FROM elbat t1;

Upvotes: 1

Related Questions