Oracle SQL select query

Question

I have this table:

select * from scott.test;

a   b   c
---------
26  R   4
26  T   5
26  S   2
25  Y   2
25  U   3
24  X   3
22  Z   3
21  P   3

What i need is this: For a given "a" i have to extract all the details (a,b,c) and if there are more then one "a", i want to extract only the one with the highest "c". Example, for 26, i want to extract only:

a  b  c
26 T  5

I have done this in this way:

select * from scott.test where a = 26 and c = (select max(c) from scott.test where a = 26);

but I need it with only one select (not select within select). Could it be done? Thank you!

Answer 1

Try this. It probably should work :).

  select a,max(b) keep (dense_rank first order by c desc) b , max(c) keep (dense_rank first order by c desc) c from test
    where a = 26
    group by a

Answer 2

Using ROW_NUMBER() analytic function you could achieve it. However, you will need to do it using a sub-query. But you could always look it different using WITH clause.

The below query would prompt you to enter the value for column a, and return you the row with maximum value of column c. The first part of the WITH clause is only to build sample data for demonstration, you only need the second part, i.e. t.

For example,

Enter value for a as 26:

SQL> WITH data AS
  2    (SELECT 26 AS a, 'R' AS b, 4 AS c FROM dual
  3    UNION ALL
  4    SELECT 26 AS a, 'T' AS b, 5 AS c FROM dual
  5    UNION ALL
  6    SELECT 26 AS a, 'S' AS b, 2 AS c FROM dual
  7    UNION ALL
  8    SELECT 25 AS a, 'Y' AS b, 2 AS c FROM dual
  9    UNION ALL
 10    SELECT 25 AS a, 'U' AS b, 3 AS c FROM dual
 11    UNION ALL
 12    SELECT 24 AS a, 'X' AS b, 3 AS c FROM dual
 13    UNION ALL
 14    SELECT 22 AS a, 'Z' AS b, 3 AS c FROM dual
 15    UNION ALL
 16    SELECT 21 AS a, 'P' AS b, 3 AS c FROM dual
 17    ),
 18    t AS
 19    ( SELECT a,b,c,row_number() OVER(PARTITION BY a ORDER BY c DESC) rn FROM DATA
 20    )
 21  SELECT a,b,c FROM t WHERE a = &1 AND rn = 1;
Enter value for 1: 26
old  21: SELECT a,b,c FROM t WHERE a = &1 AND rn = 1
new  21: SELECT a,b,c FROM t WHERE a = 26 AND rn = 1

         A B          C
---------- - ----------
        26 T          5

Enter value for a as 25:

SQL> WITH data AS
  2    (SELECT 26 AS a, 'R' AS b, 4 AS c FROM dual
  3    UNION ALL
  4    SELECT 26 AS a, 'T' AS b, 5 AS c FROM dual
  5    UNION ALL
  6    SELECT 26 AS a, 'S' AS b, 2 AS c FROM dual
  7    UNION ALL
  8    SELECT 25 AS a, 'Y' AS b, 2 AS c FROM dual
  9    UNION ALL
 10    SELECT 25 AS a, 'U' AS b, 3 AS c FROM dual
 11    UNION ALL
 12    SELECT 24 AS a, 'X' AS b, 3 AS c FROM dual
 13    UNION ALL
 14    SELECT 22 AS a, 'Z' AS b, 3 AS c FROM dual
 15    UNION ALL
 16    SELECT 21 AS a, 'P' AS b, 3 AS c FROM dual
 17    ),
 18    t AS
 19    ( SELECT a,b,c,row_number() OVER(PARTITION BY a ORDER BY c DESC) rn FROM DATA
 20    )
 21  SELECT a,b,c FROM t WHERE a = &1 AND rn = 1;
Enter value for 1: 25
old  21: SELECT a,b,c FROM t WHERE a = &1 AND rn = 1
new  21: SELECT a,b,c FROM t WHERE a = 25 AND rn = 1

         A B          C
---------- - ----------
        25 U          3

Answer 3

Like this? -

WITH test
     AS (SELECT 26 AS a, 'R' AS b, 4 AS c FROM dual
         UNION ALL
         SELECT 26 AS a, 'T' AS b, 5 AS c FROM dual
         UNION ALL
         SELECT 26 AS a, 'S' AS b, 2 AS c FROM dual
         UNION ALL
         SELECT 25 AS a, 'Y' AS b, 2 AS c FROM dual
         UNION ALL
         SELECT 25 AS a, 'U' AS b, 3 AS c FROM dual
         UNION ALL
         SELECT 24 AS a, 'X' AS b, 3 AS c FROM dual
         UNION ALL
         SELECT 22 AS a, 'Z' AS b, 3 AS c FROM dual
         UNION ALL
         SELECT 21 AS a, 'P' AS b, 3 AS c FROM dual)
SELECT a, b, c
  FROM (SELECT a, b, c, max(c) OVER (PARTITION BY a) AS max_c
          FROM test
        WHERE A = 26)
 WHERE c = max_c