CODEWITHSUNDEEP
oracle-database
Hong Van Vit
Hong Van Vit

Reputation: 2976

how can list row with max value of one column

I have some database like example below.

WITH TB AS(
     SELECT 'A' C1, 'B' AS C2, 1 AS N1, 1 AS N2 FROM DUAL UNION ALL
     SELECT 'A' C1, 'B' AS C2, 2 AS N1, 2 AS N2 FROM DUAL UNION ALL
     SELECT 'A1' C1, 'B1' AS C2, 1 AS N1, 3 AS N2 FROM DUAL UNION ALL
     SELECT 'A1' C1, 'B1' AS C2, 2 AS N1, 4 AS N2 FROM DUAL UNION ALL
     SELECT 'A1' C1, 'B1' AS C2, 3 AS N1, 1 AS N2 FROM DUAL UNION ALL
     SELECT 'A2' C1, 'B2' AS C2, 1 AS N1, 6 AS N2 FROM DUAL 
)
SELECT * FROM TB

How can I list all row with Max(N1), and group by C1, C2 like Image below?

enter image description here

Upvotes: 2

Views: 52

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

One way is using KEEP .. DENSE_RANK

SQL Fiddle

Query 1:

    WITH TB AS(
         SELECT 'A' C1, 'B' AS C2, 1 AS N1, 1 AS N2 FROM DUAL UNION ALL
         SELECT 'A' C1, 'B' AS C2, 2 AS N1, 2 AS N2 FROM DUAL UNION ALL
         SELECT 'A1' C1, 'B1' AS C2, 1 AS N1, 3 AS N2 FROM DUAL UNION ALL
         SELECT 'A1' C1, 'B1' AS C2, 2 AS N1, 4 AS N2 FROM DUAL UNION ALL
         SELECT 'A1' C1, 'B1' AS C2, 3 AS N1, 1 AS N2 FROM DUAL UNION ALL
         SELECT 'A2' C1, 'B2' AS C2, 1 AS N1, 6 AS N2 FROM DUAL 
    )
   SELECT C1
    ,C2
    ,MAX(N1) AS N1
    ,MAX(N2) KEEP (
        DENSE_RANK FIRST ORDER BY N1 DESC
        ) AS N2
FROM TB
GROUP BY C1
    ,C2

Results:

| C1 | C2 | N1 | N2 |
|----|----|----|----|
|  A |  B |  2 |  2 |
| A1 | B1 |  3 |  1 |
| A2 | B2 |  1 |  6 |

Upvotes: 2

krokodilko
krokodilko

Reputation: 36087

The description of the requirements in the question is unclear, I am guesing that max(n1) should be calculated for each group of C1+c2.
If this is a case, then you can use MAX() OVER () analytic function in this way:

SELECT tb.*,
       max( n1 ) over (partition by c1, c2 ) xxxx
FROM TB;

| C1 | C2 | N1 | N2 | XXXX |
|----|----|----|----|------|
|  A |  B |  1 |  1 |    2 |
|  A |  B |  2 |  2 |    2 |
| A1 | B1 |  1 |  3 |    3 |
| A1 | B1 |  2 |  4 |    3 |
| A1 | B1 |  3 |  1 |    3 |
| A2 | B2 |  1 |  6 |    1 |

and then wrap the above query as a subquery, and filter out unwanted rows:

SELECT c1,c2,n1,n2 FROM (
  SELECT tb.*,
       max( n1 ) over (partition by c1, c2 ) xxxx
  FROM TB
)
WHERE n1 = xxxx

| C1 | C2 | N1 | N2 |
|----|----|----|----|
|  A |  B |  2 |  2 |
| A1 | B1 |  3 |  1 |
| A2 | B2 |  1 |  6 |

Demo: http://sqlfiddle.com/#!4/d2fb9/4

Upvotes: 3

Related Questions