Oracle optimise SQL query - Multiple Max()

Question

I have a table where first I need to select data by max(event_date) then need to filter the data by max(event_sequence) then filter again by max(event_number)

I wrote following query which works but takes time.

Here the the query

SELECT DISTINCT a.stuid,
                   a.prog,
                   a.stu_prog_id,
                   a.event_number,
                   a.event_date,
                   a.event_sequence,
                   a.prog_status
   FROM table1 a
   WHERE a.event_date=
       (SELECT max(b.event_date)
        FROM table1 b
        WHERE a.stuid=b.stuid
          AND a.prog=b.prog
          AND a.stu_prog_id=b.stu_prog_id)
     AND a.event_seq=
       (SELECT max(b.event_sequence)
        FROM table1 b
        WHERE a.stuid=b.stuid
          AND a.prog=b.prog
          AND a.stu_prog_id=b.stu_prog_id
          AND a.event_date=b.event_date)
     AND a.event_number=
       (SELECT max(b.event_number)
        FROM table1 b
        WHERE a.stuid=b.stuid
          AND a.prog=b.prog
          AND a.stu_prog_id=b.stu_prog_id
          AND a.event_date=b.event_date
          AND a.event_sequence=b.event_sequence

I was wondering is there there a faster way to get the data? I am using Oracle 12c.

Tim Biegeleisen · Accepted Answer · 2019-09-23 04:30:40Z

2

You could try rephrasing your query using analytic functions:

SELECT
    stuid,
    prog,
    stu_prog_id,
    event_number,
    event_date,
    event_sequence,
    prog_status
FROM
(
    SELECT t.*,
        RANK() OVER (PARTITION BY studio, prog, stu_prog_id
                     ORDER BY event_date DESC) rnk1,
        RANK() OVER (PARTITION BY studio, prog, stu_prog_id, event_date
                     ORDER BY event_sequence DESC) rnk2,
        RANK() OVER (PARTITION BY studio, prog, stu_prog_id, event_date, event_sequence
                     ORDER BY event_number DESC) rnk3
    FROM table1 t
) t
WHERE rnk1 = 1 AND rnk2 = 1 AND rnk3 = 1;

Note: I don't actually know if you really need all three subqueries there. Adding sample data to your question might help someone else improve upon the solution I have given above.

answered Sep 23, 2019 at 4:30

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Learner Over a year ago

Thanks for reply. I tried this query but it does not work as expected(exclude some prog). I will try to upload the data.

Gordon Linoff · Accepted Answer · 2019-09-23 11:58:45Z

1

I think you want a simple row_number() or rank():

select t1.*
from (select t1.*,
             rank() over (partition by stuid, prog, stu_prog_id
                          order by event_date desc, event_sequence desc, event_number desc
                         ) as seqnum
      from table1 t1
     ) t1
where seqnum = 1;

answered Sep 23, 2019 at 11:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Learner Over a year ago

It works:) I could not up vote you as I have less than 15 reputation.

Popeye · Accepted Answer · 2019-09-23 05:22:58Z

If you have multiple records with EVENT_DATE, EVENT_SEQUENCE, EVENT_NUMBER as max respectively then in Tim's solution, Use DENSE_RANK or use the following to fetch the exact max and compare with original column data.

SELECT DISTINCT
    A.STUID,
    A.PROG,
    A.STU_PROG_ID,
    A.EVENT_NUMBER,
    A.EVENT_DATE,
    A.EVENT_SEQUENCE,
    A.PROG_STATUS
FROM
    (
        SELECT
            A.STUID,
            A.PROG,
            A.STU_PROG_ID,
            A.EVENT_NUMBER,
            A.EVENT_DATE,
            A.EVENT_SEQUENCE,
            A.PROG_STATUS,
            MAX(A.EVENT_DATE) OVER(
                PARTITION BY A.STUID, A.PROG, A.STU_PROG_ID
            ) AS MAX_EVENT_DATE,
            MAX(A.EVENT_SEQUENCE) OVER(
                PARTITION BY A.STUID, A.PROG, A.STU_PROG_ID, A.EVENT_DATE
            ) AS MAX_EVENT_SEQUENCE,
            MAX(A.EVENT_NUMBER) OVER(
                PARTITION BY A.STUID, A.PROG, A.STU_PROG_ID, A.EVENT_DATE, A.EVENT_SEQUENCE
            ) AS MAX_EVENT_NUMBER
        FROM
            TABLE1 A
    ) A
WHERE
    A.MAX_EVENT_DATE = A.EVENT_DATE
    AND A.MAX_EVENT_SEQUENCE = A.EVENT_SEQUENCE
    AND A.MAX_EVENT_NUMBER = A.EVENT_NUMBER;

Cheers!!

Barbaros Özhan · Accepted Answer · 2019-09-23 06:41:56Z

0

As being an Oracle 12c user, you can use

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

syntax as :

SELECT DISTINCT a.stuid,
                a.prog,
                a.stu_prog_id,
                a.event_number,
                a.event_date,
                a.event_sequence,
                a.prog_status
  FROM table1 a
 ORDER BY event_date DESC, event_sequence DESC, event_number DESC
 FETCH FIRST 1 ROW ONLY;

where WITH TIES clause is not needed for your case, since you're looking for DISTINCT rows, and OFFSET is not needed either, since starting point is just the beginning of a descendingly ordered columns. Even, using the keyword ROW as ROWS is optional, even for the case of plural rows such as FETCH FIRST 5 ROW ONLY; ^^ --> ROWS without S

Demo

edited Sep 23, 2019 at 6:41

answered Sep 23, 2019 at 5:13

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

3 Comments

Popeye Over a year ago

I think this will return only one row or number of rows but not first row from each group.

Barbaros Özhan Over a year ago

@Tejash No, there's no problem per each group, since each column is restricted among each other step by step. Consider this. event_sequence is filtered by event_date, and event_number is filtered by event_date and event_sequence.

Popeye Over a year ago

There will be max event date for each stuid, prog and stu_prog_id.

Collectives™ on Stack Overflow

Oracle optimise SQL query - Multiple Max()

4 Answers 4

1 Comment

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related