Group By and First Clause not working correctly

[storm75m]

I'm gonna pull my hair out!
I'm trying to group by one field, and display the first value of that group in another field, but it is not picking up the first value, even though that appears to be the first record in the table. Example
DrftNo CustNo
1 005
1 006
1 120
1 305
2 007
2 122

Expected Results
DrftNo FirstCustNo
1 005
2 007

Results I'm getting
DrftNo FirstCustNo
1 120
2 122

My SQL statement
SELECT ACHCustomers.DRFTNO, First(ACHCustomers.CustNo) AS FirstOfCustNo
FROM ACHCustomers
GROUP BY ACHCustomers.DRFTNO;

What am I doing wrong? The data in the table (appears to be) in order by CustNo. I even based my query on a separate query that specifically sorts the table by CustNo. Is there something I need to do with indexes? Any help would be much appreciated, thank you.

 

[PHV]

Use the Min aggregate function instead of First.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[storm75m]

I thought of that just as I was clicking 'submit' and it works fine, thanks for the speedy response.