Finding MAX of Column in Oracle SQL

Question

I have the following table structure:

Table A (A1, ...) where A1 is PK

Table B (B1, B2, ...) where A.A1 = B.B1 and B2 is PK 

Table C (C1, C2, C3, ...) where C.C1 = B.B2 and C1 is PK

Given A.A1 and C.C2 are provided, I need to obtain the MAX() of C3. The following apparently does not work:

select c.c3
from A a join B b on a.A1 = b.B1
         join C c on b.B2 = c.C1
where a.A1 = '123'
  and c.C2 = to_date('01-01-2000', 'mm-dd-yyyy')
  and c.C3 = (
    select max(c3)
    from C
    where c1 = c.C1)

And the query returns nothing. However, when I try:

select max(c.c3)
from A a join B b on a.A1 = b.B1
         join C c on b.B2 = c.C1
group by a.A1, c.C2
having a.A1 = '123'
  and c.C2 = to_date('01-01-2000', 'mm-dd-yyyy')

It seems like it returns the right value however it is slow. Is there a way to get it to work with the first query (no partitioning)?

score 1 · Accepted Answer · edited Apr 19 '15 at 01:16

1

You can try WITH clause.

Your query could be something like this:

;WITH C AS(
    select c.c3
    from A a join B b on a.A1 = b.B1
             join C c on b.B2 = c.C1
    where a.A1 = '123'
      and c.C2 = to_date('01-01-2000', 'mm-dd-yyyy')
)
SELECT MAX(C3) FROM C

Or simply,

select max(cs)
from (
    select c.c3 as cs
    from A a join B b on a.A1 = b.B1
             join C c on b.B2 = c.C1
    where a.A1 = '123'
      and c.C2 = to_date('01-01-2000', 'mm-dd-yyyy')
)

edited Apr 19 '15 at 01:16

Ulvon

91
1
6

answered Apr 19 '15 at 00:59

sqluser

5,502
7
36
50

Thanks. I added a one-query solution to your answer. I'm just wondering why the first query I posted does not work. – Ulvon Apr 19 '15 at 01:15

score 1 · Answer 2 · answered Apr 19 '15 at 02:21

1

Why not just use max(c3) in the select?

select max(c.c3)
from A a join
     B b
     on a.A1 = b.B1 join
     C c
     on b.B2 = c.C1
where a.A1 = '123' and c.C2 = date '2000-01-01';

answered Apr 19 '15 at 02:21

Gordon Linoff

1,242,037
58
646
786

Finding MAX of Column in Oracle SQL

2 Answers2