CODEWITHSUNDEEP
sqloracle-database
Susham Nandi
Susham Nandi

Reputation: 23

Remove multiple entries for a column

I need two columns A and B but of them A has repeated values and B has single unique values. I have to fetch only those values of A which has max(C) value. C is another column.

Upvotes: 1

Views: 71

Answers (4)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

You can use ROW_NUMBER.

ROW_NUMBER

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

PARTITION BY value_expression

Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. value_expression specifies the column by which the result set is partitioned. If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

ORDER BY

The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. It is required.

Sample of ROW_NUMBER in your case:

SELECT A, B 
FROM
(
   SELECT ROW_NUMBER() OVER(PARTITION BY A ORDER BY C DESC) AS RowNum, A, B, C
   FROM   TableName
)
WHERE RowNum = 1

Upvotes: 2

Boneist
Boneist

Reputation: 23578

An alternative to @NoDisplayName's solution is to use keep dense_rank first/last:

with your_table as (select 1 a, 3 b, 10 c from dual union all
                    select 1 a, 2 b, 20 c from dual union all
                    select 1 a, 1 b, 30 c from dual union all
                    select 2 a, 4 b, 40 c from dual union all
                    select 2 a, 5 b, 60 c from dual union all
                    select 2 a, 3 b, 60 c from dual union all
                    select 3 a, 6 b, 70 c from dual union all
                    select 4 a, 2 b, 80 c from dual)
select a,
       max(b) keep (dense_rank first order by c desc) b,
       max(c) max_c
from   your_table
group by a;

         A          B      MAX_C
---------- ---------- ----------
         1          1         30
         2          5         60
         3          6         70
         4          2         80

Upvotes: 1

SouravA
SouravA

Reputation: 5243

Using the INTERSECT keyword get those rows which have maximum value of ColC for the ColA.

select ColA, ColB from

(
 select ColA, ColB, max(colC) from Tabl 
 group by ColA, ColB

 intersect

 select ColA, ColB, ColC from Tabl
) as A

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Use Row_Number Analytic function to do this

select A,B 
from
(
select row_number() over(partition by A order by C desc)rn,A,B,C
from yourtable
)
where RN=1

Upvotes: 1

Related Questions