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!

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. — Lalit Kumar B, Oct 20 '15 at 15:03
@LalitKumarB I think he wants the answer asap (as soon as possible) - *which I think is quite rude* — Radu Gheorghiu, Oct 20 '15 at 15:04

score 1 · Answer 1 · answered Oct 20 '15 at 15:38

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

Stawros · Answer 2 · 2015-10-20T15:10:21.783

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

edited Oct 20 '15 at 15:10

answered Oct 20 '15 at 15:07

Stawros

918
1
10
20

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

score 0 · Answer 3 · answered Oct 20 '15 at 20:40

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

answered Oct 20 '15 at 20:40

Arkadiusz Łukasiewicz

6,241
1
11
17

Oracle SQL select query

3 Answers3