2

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!

Improve this question
3
  • 2
    You need to use CASE and ROW_NUMBER analytic function. And what do you mean by asap in your question? To get an answer ASAP, you should provide a working test case. Create and insert statements. At least SQL Fiddle. Showing the data is no help to who wants to help you. You execute a SQL on a table, so just looking at the data people could only GUESS and not give you perfectly correct answer. Commented Oct 20, 2015 at 15:03
  • 3
    @LalitKumarB I think he wants the answer asap (as soon as possible) - which I think is quite rude Commented Oct 20, 2015 at 15:04
  • @RaduGheorghiu I know. I am explaining that to OP. Commented Oct 20, 2015 at 15:18

3 Answers 3

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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
Improve this answer

3 Comments

It doesn't really look like .. one select, but two, even if they're nested.
If @user3913719 told about single table scan, then doesn't matter how many subqueries is. Dunno what "not select within select" is mean :)
Yes, I agree with you, but did OP mention anything about table scans?
0

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.