Who have the max value : MAX « Aggregate Functions « Oracle PL / SQL

1. Oracle PL / SQL
2. Aggregate Functions
3. MAX

Who have the max value

   


SQL>
SQL> create table gift(
  2           gift_id                integer         primary key
  3          ,emp_id                integer
  4          ,register_date              date not null
  5          ,total_price        number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment        varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,message        varchar2(100)
 12  );

Table created.

SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
  2                 (1,1,'14-Feb-1999', 123.12, '14-Feb-1999', '12 noon', 'CA',1, null, 'Happy Birthday to you');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
  2                 (2,1,'14-Feb-1999', 50.98, '14-feb-1999', '1 pm', 'CA',7, 'name1', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (3, 2,'14-Feb-1999', 35.99, '14-feb-1999', '1 pm', 'VS',2, 'Tom', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (4, 2,'14-Feb-1999', 19.95, '14-feb-1999', '5 pm', 'CA',2, 'Mary', 'Happy Birthday');

1 row created.

SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (5, 6,'4-mar-1999', 10.95, '5-mar-1999', '4:30 pm', 'VS', 2, 'Jack', 'Happy Birthday');

1 row created.

SQL>
SQL> select gift_id, total_price
  2    from gift
  3   where total_price =
  4      (select max(total_price) from gift);

   GIFT_ID TOTAL_PRICE
---------- -----------
         1      123.12

SQL>
SQL> drop table gift;

Table dropped.

   
    
    
  

Related examples in the same category