CODEWITHSUNDEEP
sqloracle-databaseplsql
Muhammad Yakubov
Muhammad Yakubov

Reputation: 69

Error result for define max value

When I want to select max value from code (code below). It shows me 2 max row result. But I want to get one max value. Code below:

select 
distinct(c.msisdn), 
max(adj_date),
6144 - (round(t.volume /1024/1024,2))as остаток_мб 
from 
subscriber_discount_threads t, 
client_balance c, 
subs_packs p 
where 
t.dctr_dctr_id = 1283 
and p.pack_pack_id in (877,874) 
and c.msisdn = '550350057' 
and t.adj_date >= '30.10.2015' 
and sysdate between p.start_date and p.end_date 
and sysdate between t.start_thread and t.end_thread  
and c.subs_subs_id = t.subs_subs_id 
and p.subs_subs_id = c.subs_subs_id 
group by c.msisdn, t.volume

result below (2 row, but i want that shows me only max date)

25.11.2015 13:08:06
03.11.2015 11:42:06

What could be the problem?

Upvotes: 0

Views: 56

Answers (3)

are
are

Reputation: 2615

I think you need use analytic functions to get the information

select distinct c.msisdn, t.volume, adj_date,
max(adj_date) over (partition by c.msisdn, t.volume) as max_adj_date,
6144 - (round(t.volume /1024/1024,2))as остаток_мб 
from 
subscriber_discount_threads t, 
client_balance c, 
subs_packs p 
where 
t.dctr_dctr_id = 1283 
and p.pack_pack_id in (877,874) 
and c.msisdn = '550350057' 
and t.adj_date >= '30.10.2015' 
and sysdate between p.start_date and p.end_date 
and sysdate between t.start_thread and t.end_thread  
and c.subs_subs_id = t.subs_subs_id 
and p.subs_subs_id = c.subs_subs_id

Upvotes: 0

Utsav
Utsav

Reputation: 8093

Edit: Updated my answer as per OP

    with cte as(
        select 
        distinct(c.msisdn) as msisdn, 
        max(adj_date) as maxdate,
        6144 - (round(t.volume /1024/1024,2))as остаток_мб ,
        from 
        subscriber_discount_threads t, 
        client_balance c, 
        subs_packs p 
        where 
        t.dctr_dctr_id = 1283 
        and p.pack_pack_id in (877,874) 
        and c.msisdn = '550350057' 
        and t.adj_date >= '30.10.2015' 
        and sysdate between p.start_date and p.end_date 
        and sysdate between t.start_thread and t.end_thread  
        and c.subs_subs_id = t.subs_subs_id 
        and p.subs_subs_id = c.subs_subs_id 
        group by c.msisdn, t.volume
    ),
    cte2 as(
    select c1.*,row_number over(partition by msisdn order by maxdate desc) as maxdatecol from cte c1)
    select c2.* from cte2 c2 
    where maxdatecol =1
    ;

Upvotes: 1

Tatiana
Tatiana

Reputation: 1499

You can try this one:

select 
distinct c.msisdn, 
max(adj_date) over (order by null),
6144 - (round(t.volume /1024/1024,2))as остаток_мб 
from 
subscriber_discount_threads t, 
client_balance c, 
subs_packs p 
where 
t.dctr_dctr_id = 1283 
and p.pack_pack_id in (877,874) 
and c.msisdn = '550350057' 
and t.adj_date >= '30.10.2015' 
and sysdate between p.start_date and p.end_date 
and sysdate between t.start_thread and t.end_thread  
and c.subs_subs_id = t.subs_subs_id 
and p.subs_subs_id = c.subs_subs_id

max value will be the same for all rows. It is max date in table. Not sure if it is what you want, but this is how I understand you.

Upvotes: 0

Related Questions