InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 07:25:48 GMT20http://www.sqlservercentral.com/Forums/Topic1374071-392-1.aspxOn the MS Access UK site, another suggestion was this.It provided the exact same results as my solution above.Did not see any difference in the time for 100,000 records.It uses the TOP and Order By Thought it would be good to share this too.MS Access developers could use this solution and be able to migrate to T-SQL easily.SELECT ID_Wells, ID_SHLBHL, Req_Fin_Priority AS MinPriority, Req_Fin, SHLBHLFROM dbo.Sub_1WHERE (Req_Fin_Priority = (SELECT TOP (1) Req_Fin_Priority AS Expr1 FROM dbo.Sub_1 AS Sub_1_1 WHERE (dbo.Sub_1.ID_Wells = ID_Wells) ORDER BY Expr1))Fri, 19 Oct 2012 08:51:50 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic1374071-392-1.aspxSlugged through it and came up with this.SELECT ID_Wells, Req_Fin_Priority AS MinPriority, ID_SHLBHL, Req_Fin, SHLBHLFROM dbo.Sub_1WHERE (Req_Fin_Priority = (SELECT MIN(Req_Fin_Priority) AS Expr1 FROM dbo.Sub_1 AS Sub_1_1 WHERE (dbo.Sub_1.ID_Wells = ID_Wells)))Thu, 18 Oct 2012 08:48:11 GMTMile Higher Than Sea Levelhttp://www.sqlservercentral.com/Forums/Topic1374071-392-1.aspxCan someone give me some support in migrating this to SQL Server? The Req_Fin_Priorities are numeric (1, 2, or 3 no nulls).In Access, the[b] DMin [/b]function returns the minimum value in a specified set of records (or domain). The syntax for the DMin function is: DMin ( expression, domain, [criteria] )In TSQL - it appears to need: SELECT Min(column) FROM table WHERE condition[b]Access SQL Sub_2 (works)[/b]SELECT sub_1.ID_Wells, sub_1.Req_Fin_Priority AS MinPriority, sub_1.ID_SHLBHLFROM sub_1WHERE (((sub_1.Req_Fin_Priority)=[b]DMin[/b]("[Req_Fin_Priority]","sub_1","[ID_Wells]=" & [ID_Wells])));[b]Access SQL sub_1 (works)[/b]SELECT Wells_SHLBHL.ID_Wells, Req_Fin_Priorities.Req_Fin_Priority, Wells_SHLBHL.Req_Fin, Wells_SHLBHL.ID_SHLBHL, Wells_SHLBHL.SHLBHLFROM Wells_SHLBHL INNER JOIN Req_Fin_Priorities ON Wells_SHLBHL.Req_Fin = Req_Fin_Priorities.Req_Fin_TypeWHERE (((Wells_SHLBHL.SHLBHL)="SHL"));Wed, 17 Oct 2012 15:44:07 GMTMile Higher Than Sea Level