Oracle sql retrive records based on maximum time

Question

i have below data.

table A

id   

1

2

3

table B

id       name      data1        data2     datetime

1        cash      12345.00                12/12/2012 11:10:12

1        quantity   222.12                  14/12/2012 11:10:12

1        date     20/12/2012               12/12/2012 11:10:12

1        date     19/12/2012                13/12/2012 11:10:12

1        date     13/12/2012                14/12/2012 11:10:12

1        quantity   330.10                   17/12/2012 11:10:12

I want to retrieve data in one row like below:

tableA.id      tableB.cash        tableB.date     tableB.quantity 

1               12345.00          13/12/2012       330.10

I want to retrieve based on max(datetime).

Answer 1

The data model appears to be insane-- it makes no sense to join an ORDER_ID to a CUSTOMER_ID. It makes no sense to store dates in a VARCHAR2 column. It makes no sense to have no relationship between a CUSTOMER and an ORDER. It makes no sense to have two rows in the ORDER table with the same ORDER_ID. ORDER is also a reserved word so you cannot use that as a table name. My best guess is that you want something like

select * 
  from customer c 
       join (select order_id, 
                    rank() over (partition by order_id 
                                     order by to_date( order_time, 'YYYYMMDD HH24:MI:SS' ) desc ) rnk
               from order) o on (c.customer_id=o.order_id)
 where o.rnk = 1

If that is not what you want, please (as I asked a few times in the comments) post the expected output.

These are the results I get with my query and your sample data (fixing the name of the ORDER table so that it is actually valid)

SQL> ed
Wrote file afiedt.buf

  1  with orders as (
  2    select 1 order_id, 'iphone' order_name, '20121201 12:20:23' order_time from dual union all
  3    select 1, 'iphone', '20121201 12:22:23' from dual union all
  4    select 2, 'nokia', '20110101 13:20:20' from dual ),
  5   customer as (
  6    select 1 customer_id, 'paul' customer_name from dual union all
  7    select 2, 'stuart' from dual union all
  8    select 3, 'mike' from dual
  9  )
 10  select *
 11    from customer c
 12         join (select order_id,
 13                      rank() over (partition by order_id
 14                                       order by to_date( order_time, 'YYYYMMDD HH24:MI:SS' ) desc ) rnk
 15                 from orders) o on (c.customer_id=o.order_id)
 16*  where o.rnk = 1
SQL> /

CUSTOMER_ID CUSTOM   ORDER_ID        RNK
----------- ------ ---------- ----------
          1 paul            1          1
          2 stuart          2          1

Answer 2

Try something like

SELECT *
  FROM CUSTOMER c
  INNER JOIN ORDER o
    ON (o.CUSTOMER_ID = c.CUSTOMER_ID)
  WHERE TO_DATE(o.ORDER_TIME, 'YYYYMMDD HH24:MI:SS') =
    (SELECT MAX(TO_DATE(o.ORDER_TIME, 'YYYYMMDD HH24:MI:SS')) FROM ORDER)

Share and enjoy.