SQL query logic for selecting a unique subset of records from a many to many table.

Question

I've run into quite a tough problem for my novice SQL query writing skills. I'll try and describe my problem as best I can.

Table1: Account, Date, Time, Area, ResultValue, ResultString, Phone, GUID

Table2: Name, Account, phone, GUID

For each record in Table2 I need the the single record from Table1 that is most accurate to the following criteria: Lowest ResultValue then latest time.

The final result needs to show the most appropriate result and the total # of results based on the phone number.

Final results: Name, Account, phone, Date, Time, ResultString, Attempts

I've done all this but with a painfully slow process of about 9 queries. I can't help but think there is a better way.

Answer 1

Maybe this article can bring you in the right direction?

http://www.bennadel.com/blog/1093-Getting-One-Record-Per-Group-From-A-One-to-Many-Join.htm

Or this one

http://stackoverflow.com/questions/1563148/one-to-many-query-selecting-all-parents-and-single-top-child-for-each-parent