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.
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.