StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The data model appears to be insane-- it makes no sense to join an <code>ORDER_ID</code> to a <code>CUSTOMER_ID</code>. It makes no sense to store dates in a <code>VARCHAR2</code> column. It makes no sense to have no relationship between a <code>CUSTOMER</code> and an <code>ORDER</code>. It makes no sense to have two rows in the <code>ORDER</code> table with the same <code>ORDER_ID</code>. <code>ORDER</code> is also a reserved word so you cannot use that as a table name. My best guess is that you want something like</p> <pre><code>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 </code></pre> <p>If that is not what you want, please (as I asked a few times in the comments) post the expected output.</p> <p>These are the results I get with my query and your sample data (fixing the name of the <code>ORDER</code> table so that it is actually valid)</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload