CODEWITHSUNDEEP
oracle-database
Chetan
Chetan

Reputation: 11

how to get following output in oracle using query?

I've following data like

ano         asal
------------------
1            100
1            150
1            190
2            200
2            240
3            300
3            350
4             400
4             400
4             400

i want ans like max sal from 1 ,from 2,3 and 4 o/p like

ano       asal
---------------
1          190
2          240
3          390
4          400
4          400
4          400

Upvotes: 1

Views: 32

Answers (3)

Husqvik
Husqvik

Reputation: 5809

SELECT
    ano, asal
FROM (
    SELECT
        data.*,
        MAX(asal) OVER (PARTITION BY ano) max
    FROM
        data)
WHERE
    asal = max

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You can use an union the firt select with group by 

select ano, max(asal) 
from my_table 
where ano != 4 
group by ano
union all 
select ano, asal
from my_table 
where ano = 4 
order by ano

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You want to return the max value of asal for each ano group, but you want to retain the duplicates in the original table if they exist. This means you can't just do a simple GROUP BY. But you can use a GROUP BY query to identify the max values and then retain those records via an INNER JOIN. Try this query:

SELECT t1.ano, t1.asal
FROM yourTable t1
INNER JOIN
(
    SELECT ano, MAX(asal) AS asal
    FROM yourTable
    GROUP BY ano
) t2
    ON t1.ano = t2.ano AND t1.asal = t2.asal

Upvotes: 1

Related Questions