Unanswered: MS Access vs. Transact-SQL Conditional SQL Select Statement Problem
I am trying to write a Transact-SQL statement and am having no success. I have a customers table that has some duplicate Customer Numbers in it, however the records do have a unique Reference Number. I am trying select all records that match a list of Customer Numbers but if there are more than 1 matching Customer Number I only want the one with the largest Reference Number.
My BASIC Select Statement is:
SELECT Cust_Ref_No, Customer_No, Customer_Name, Address1,
Address2, Suburb, State, Postcode, Phone
WHERE Customer_No IN
(SELECT Customer_No FROM temp_Customers)
Much to my HORROR I found that what I am trying to do is simple in MS Access using the First Function Such as below:
SELECT First(Cust_Ref_No) AS Cust_Ref_No, Customer_No
GROUP BY Customer_No
ORDER BY First(Cust_Ref_No), Customer_No;
There appears to be no First Function in Transact-SQL. Is someone able to show me how to achieve the same results in Transact-SQL as I can get with MS Access (Id HATE to think that MS Access has more functionality in querying data than SQL Server in any way at all)?
P.S. I really need to run the select statement as one step rather than splitting it up into parts.