Need help with Group By in Query

[mark1110]

I have two tables I need to run a query on. The first is has a company code and code order, the second has the customer information. I need to run a query that just lists the minimum code order for a customer based on the company code. There can be several rows based on the customer id. I just need one row returned that is based on the code order. For example

code order customer id company code
--------------------------------------
9 0011 "IL"
12 0011 "VA"
6 0011 "ME"

The only row I want returned is the last row since it has the minimum code order. I tried this:

SELECT a.code_order, b.customer_id, b.company_cd
FROM table1 a, table2 b
WHERE a.customer_id = b.customer_id
GROUP BY b.customer_id
HAVING min(a.code_order) = a.code_order

but that didn't work. Is there another way of doing this.

 

[PDreyer]

not tested

select a.code_order, a.customer_id, a.company_cd
from t2 a
join 
(select customer_id , min_code_order=min(code_order)
 from t1 b
 group by customer_id 
) b
   on a.customer_id = b.customer_id
  and a.code_order=b.min_code_order

 

[pmegan]

select first a.code_order, b.customer_id, b.company_cd 
from test1 a join test2 b on
        a.cust_id = b.cust_id
order by a.order_code