I have a question about selecting only the first record in a group. Example: I have table A with primary key = 999. Table B has multiple records with primary key = 999. How can I match Table A 999 with the first occurrence of 999 in Table B, and then extract other field data (such as street address) from the Table B record. I have tried using the Count() function, but it seems that I can only do this using cursors.
use northwind select * from orders select * from [order details]
now here's the query to simulate your needs
select a.orderid,a.productid, od2.* from ( select o.orderid, min(productid)as productid from orders o join [order details] od on o.orderid=od.orderid group by o.orderid) as a join [order details] od2 on a.orderid=od2.orderid and a.productid=od2.productid