Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with Group By in Query

Status
Not open for further replies.

mark1110

Programmer
Apr 20, 2005
85
US
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.

 
not tested
Code:
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
 
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top