CODEWITHSUNDEEP
sqloracle-databaseplsql
Splunk
Splunk

Reputation: 311

Get the most recent date without the MAX function... Oracle SQL

Yet another academic task that will have absolutely no place in the real world... This is what I have to do:

• Use an explicit cursor with a parameter that accepts car registration to find the most recent reservation made on the car. You will have to look at the date_reserved column from I_BOOKING table here. You cannot use the MAX function. Compare all the relevant dates to find the most recent one.

Honestly, I have no idea how else to do this outside of the MAX (or other) function.

So far I have this:

CURSOR reg_cr (registration VARCHAR2) IS
SELECT GREATEST (date_reserved)
FROM i_booking;
v_reg reg_cr%ROWTYPE;

If someone could point me in the right direction I would be grateful.

Upvotes: 0

Views: 6570

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

A couple of methods that return only one record:

SELECT date_reserved
FROM   (SELECT * FROM i_booking
        ORDER BY date_reserved DESC)
WHERE  ROWNUM = 1;

SELECT DISTINCT
       FIRST_VALUE(date_reserved)
       OVER (ORDER BY date_reserved DESC)
FROM   i_booking;

SELECT date_reserved
FROM   (SELECT date_reserved
              ,ROW_NUMBER() OVER (ORDER BY date_reserved DESC)
               AS the_rn
        FROM   i_booking)
WHERE  the_rn = 1;

This method may return multiple rows (if there is a tie for first place):

SELECT date_reserved
FROM   (SELECT date_reserved
              ,RANK() OVER (ORDER BY date_reserved DESC)
               AS the_rank
        FROM   i_booking)
WHERE  the_rank = 1;

wildplasser's answer also exhibits this feature.

Upvotes: 2

wildplasser
wildplasser

Reputation: 44240

The maximal value means: there is no corresponding record with a higher value:

SELECT *
FROM orders oo                       -- all orders
WHERE NOT EXISTS (                   -- for which there does not exist
   SELECT * FROM orders nx           -- an order from
   WHERE nx.client_id = oo.client_id -- the same client
   AND nx.order_date > oo.order_date -- ,but with a more recent date
   );

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191265

Although the wording of the assignment doesn't seem to explicitly preclude using a different built-in function, the spirit seems to me to be looking for a brute-force approach looping over all of the date values - particularly from the 'compare all the relevant dates' part, though that's still ambiguous. Depends what you've already learned about as well, I suppose.

So here's my take on what I thought the assignment was getting at:

set serveroutput on
declare
  cursor reg_cur (registration i_booking.registration%type) is
    select date_reserved
    from i_booking
    where registration = reg_cur.registration;
  max_date date;
begin
  for reg_row in reg_cur('<reg value>') loop
    if max_date is null or reg_row.date_reserved > max_date then
      max_date := reg_row.date_reserved;
    end if;
  end loop;
  dbms_output.put_line('Most recent date is: ' || to_char(max_date, 'YYYY-MM-DD'));
end;
/

This keeps track of the most recent date it's seen so far, and on each iteration of the loop, checks if the current cursor value if more recent. Horribly contrived of course, but that's what you seemed to be expecting.

It also isn't clear what you're supposed to do with the value; dbms_output also isn't suitable for any real world use here, so maybe you want a function; here's an SQL Fiddle of the same approach in function form.

Upvotes: 1

Related Questions