Oracle SQL - Only return value for enquiry ID with the highest enquiry_log_number

Question

I've got some code to return a list of all enquiries where one of the enquiry_status_codes are present from a predetermined list.

select
central_enquiry.enquiry_number,
central_enquiry.enquiry_time,
type_of_service.service_name,
enquiry_subject.subject_name,
central_enquiry.enq_status_code,
enquiry_status.enq_status_name,
central_enquiry.follow_up_code,
follow_up.follow_up_name,
central_enquiry.follow_up_date,

CASE
WHEN ROUND((central_enquiry.follow_up_date - SYSDATE),2) >=14 AND 
ROUND((central_enquiry.follow_up_date - SYSDATE),2) <= 100  THEN 'Over 2 Weeks'
WHEN ROUND((central_enquiry.follow_up_date - SYSDATE),2) >=7 AND 
ROUND((central_enquiry.follow_up_date - SYSDATE),2) <= 13.99  THEN '1 - 2 Weeks'
WHEN ROUND((central_enquiry.follow_up_date - SYSDATE),2) >=2 AND 
ROUND((central_enquiry.follow_up_date - SYSDATE),2) <= 6.99  THEN '2 - 7 Days'
WHEN ROUND((central_enquiry.follow_up_date - SYSDATE),2) >=1 AND 
ROUND((central_enquiry.follow_up_date - SYSDATE),2) <= 1.99  THEN 'Tomorrow'
WHEN ROUND((central_enquiry.follow_up_date - SYSDATE),2) >=0 AND 
ROUND((central_enquiry.follow_up_date - SYSDATE),2) <= 0.99  THEN 'Today'
ELSE 'Deadline Missed' 
END as Length_Paused,

central_enquiry.log_text,
enquiry_status_log.enquiry_log_number,
enquiry_status_log.enq_status_code,
enquiry_status.enq_status_name,
enquiry_status_log.follow_up_date

from
central_enquiry
inner join type_of_service on central_enquiry.service_code = type_of_service.service_code
inner join enquiry_subject on central_enquiry.subject_code = enquiry_subject.subject_code
inner join follow_up on central_enquiry.follow_up_code = follow_up.follow_up_code
inner join enquiry_status_log on central_enquiry.enquiry_number = enquiry_status_log.enquiry_number
inner join enquiry_status on enquiry_status_log.enq_status_code = enquiry_status.enq_status_code 

where
enquiry_status_log.enq_status_code in ('EN90','EN11','EN40','EN91','EN12') and
central_enquiry.outstanding_flag = 'Y' 
and
(enquiry_status_log.enq_status_code IN ('EN11', 'EN12','EN40') AND enquiry_status_log.follow_up_date 
< SYSDATE) OR
enquiry_status_log.enq_status_code IN ('EN90', 'EN91')

order by 
central_enquiry.enquiry_number, enquiry_status_log.enquiry_log_number desc

What I would like to do is restrict this so that for each enquiry_number it only returns the line with the highest enquiry_status_log.enquiry_log_number value.

I think this needs a MAX statement in there somewhere, but everything I try keeps coming back with a user error.

Edit: Have tried using the following code:

(
select 
enquiry_status_log.enquiry_number, 
enquiry_status_log.enquiry_log_number, follow_up.follow_up_name, 
enquiry_status_log.follow_up_date, 
rank() over (partition by enquiry_status_log.enquiry_number 
order by enquiry_status_log.enquiry_log_number desc) rn 
from 
enquiry_status_log 
join follow_up on enquiry_status_log.follow_up_code = 
follow_up.follow_up_code  
) 
table1 

select 
enquiry_number,   
enquiry_log_number,
follow_up_name, 
follow_up_date 
from table1 
where rn = 1 
order by enquiry_number, enquiry_log_number

Do you know how to use `group by`? – Robert Andrzejuk Nov 15 '19 at 17:26 — Robert Andrzejuk, Nov 15 '19 at 17:26

Littlefoot · Answer 1 · 2019-11-21T19:00:27.727

To me, it looks as a candidate for one of analytic functions. Have a look at this example, based on Scott's schema. Suppose I'd like to return employee(s) - per department - with the highest salary.

SQL> select d.dname, e.ename, e.job, e.sal
  2  from emp e join dept d on e.deptno = d.deptno
  3  order by d.dname, e.sal desc, e.ename;

DNAME          ENAME      JOB              SAL
-------------- ---------- --------- ----------
ACCOUNTING     KING       PRESIDENT       5000     --> highest in Accounting
ACCOUNTING     CLARK      MANAGER         2450
ACCOUNTING     MILLER     CLERK           1300

RESEARCH       FORD       ANALYST         3000     --> highest in 
RESEARCH       SCOTT      ANALYST         3000     --> Research
RESEARCH       JONES      MANAGER         2975
RESEARCH       ADAMS      CLERK           1100
RESEARCH       SMITH      CLERK            800

SALES          BLAKE      MANAGER         2850     --> highest in Sales
SALES          ALLEN      SALESMAN        1600
SALES          TURNER     SALESMAN        1500
SALES          MARTIN     SALESMAN        1250
SALES          WARD       SALESMAN        1250
SALES          JAMES      CLERK            950

14 rows selected.

SQL>

Here's how - pay attention to lines #3 and #8:

SQL> with data as
  2    (select d.dname, e.ename, e.job, e.sal,
  3       rank() over (partition by d.dname order by e.sal desc) rn
  4     from emp e join dept d on e.deptno = d.deptno
  5    )
  6  select dname, ename, job, sal
  7  from data
  8  where rn = 1
  9  order by dname, ename;

DNAME          ENAME      JOB              SAL
-------------- ---------- --------- ----------
ACCOUNTING     KING       PRESIDENT       5000
RESEARCH       FORD       ANALYST         3000
RESEARCH       SCOTT      ANALYST         3000
SALES          BLAKE      MANAGER         2850

SQL>

Try to apply it to your query.

[EDIT, based on query you posted]

It looks that you wanted to use a CTE (common table expression) a.k.a. WITH factoring clause. If that's so, then it looks like this:

WITH table1
     AS (SELECT enquiry_status_log.enquiry_number,
                enquiry_status_log.enquiry_log_number,
                follow_up.follow_up_name,
                enquiry_status_log.follow_up_date,
                RANK ()
                OVER (PARTITION BY enquiry_status_log.enquiry_number
                      ORDER BY enquiry_status_log.enquiry_log_number DESC)
                   rn
           FROM enquiry_status_log
                JOIN follow_up
                   ON enquiry_status_log.follow_up_code =
                         follow_up.follow_up_code)
  SELECT enquiry_number,
         enquiry_log_number,
         follow_up_name,
         follow_up_date
    FROM table1
   WHERE rn = 1
ORDER BY enquiry_number, enquiry_log_number;

[EDIT #2, an inline view]

  SELECT enquiry_number,
         enquiry_log_number,
         follow_up_name,
         follow_up_date
    FROM (SELECT enquiry_status_log.enquiry_number,
                 enquiry_status_log.enquiry_log_number,
                 follow_up.follow_up_name,
                 enquiry_status_log.follow_up_date,
                 RANK ()
                 OVER (PARTITION BY enquiry_status_log.enquiry_number
                       ORDER BY enquiry_status_log.enquiry_log_number DESC)
                    rn
           FROM enquiry_status_log
                JOIN follow_up
                   ON enquiry_status_log.follow_up_code =
                         follow_up.follow_up_code)
   WHERE rn = 1
ORDER BY enquiry_number, enquiry_log_number

Have tried amending the code to the following: `code` (select enquiry_status_log.enquiry_number, enquiry_status_log.enquiry_log_number, follow_up.follow_up_name, enquiry_status_log.follow_up_date, rank() over (partition by enquiry_status_log.enquiry_number order by enquiry_status_log.enquiry_log_number desc) rn from enquiry_status_log join follow_up on enquiry_status_log.follow_up_code = follow_up.follow_up_code ) table1 select enquiry_number, enquiry_log_number, follow_up_name, follow_up_date from table1 where rn = 1 order by enquiry_number, enquiry_log_number — Rob Morris, Nov 21 '19 at 11:09
However this just brings back and error message: ORA-00933: SQL Command not properly ended — Rob Morris, Nov 21 '19 at 11:09
This is difficult to read and is invalid; please, post it by editing your original question. If possible, create test case (CREATE TABLE and INSERT INTO) so that we'd be able to reproduce the problem. — Littlefoot, Nov 21 '19 at 13:32
Have added updated code to original question that should make it clearer to follow. — Rob Morris, Nov 21 '19 at 13:38
Thank you; I edited my answer by adding some more info, as well as your code - rewritten so that it looks as it should. — Littlefoot, Nov 21 '19 at 14:02
The system I'm using doesn't allow CTE to be used? Do you know how this would be written in an inline view where SELECT is the first word in the coding? — Rob Morris, Nov 21 '19 at 16:20

Oracle SQL - Only return value for enquiry ID with the highest enquiry_log_number

1 Answers1