Convert Access SQL DMin to T-SQL Min - assistance

[Rx_]

Can someone give me some suggestions in how to convert the Sub_2 to SQL Server? The Req_Fin_Priorities are numeric (1, 2, or 3 no nulls).

In Access, the DMin 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

Access SQL Sub_2 (works)
SELECT sub_1.ID_Wells, sub_1.Req_Fin_Priority AS MinPriority, sub_1.ID_SHLBHL
FROM sub_1
WHERE (((sub_1.Req_Fin_Priority)=DMin("[Req_Fin_Priority]","sub_1","[ID_Wells]=" & [ID_Wells])));

Access SQL sub_1 (works) - Two Numeric, two Text, numeric, text SELECT Wells_SHLBHL.ID_Wells, Req_Fin_Priorities.Req_Fin_Priority, Wells_SHLBHL.Req_Fin, Wells_SHLBHL.ID_SHLBHL, Wells_SHLBHL.SHLBHL
FROM Wells_SHLBHL INNER JOIN Req_Fin_Priorities ON Wells_SHLBHL.Req_Fin = Req_Fin_Priorities.Req_Fin_Type
WHERE (((Wells_SHLBHL.SHLBHL)="SHL"));

The MS Access SQL works OK, gets the right answer. But, due to the cursor, it takes about 4 minutes to run each set of 15,000 records. There are 4 sets. Need to move this one to SQL Sever to utalize larger resources and avoid linked tables.

 

[Rx_]

This is what I came up with. Couldn't find anything on the internet to help and thought it might be useful. Will verify the the data counts against Access now.
This runs in parts of a second on SQL Server against the same data set.

SELECT     ID_Wells, Req_Fin_Priority AS MinPriority, ID_SHLBHL, Req_Fin, SHLBHL
FROM         dbo.Sub_1
WHERE     (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)))

 

[mdlueck]

I would have coded it to use the solution I posted here...

http://www.access-programmers.co.uk/forums/showthread.php?p=1196447#post1196419

For the subquery aspect.

 

[Rx_]

It is a subtle difference, did use this very example at first.
An individual table record can be updated or entered (or edited) out of order.
It is a business rule status (Req_Fin_Priorities) that make the final determination. Different status can arrive at different times so the data entry is not consistantly in order. And, a past value can be updated to Final while a previous / past "final" was changed to "moved" or "Sumbitted".

The T-SQL data values did return the exact same numbers as the MS Access SQL. The big difference being, Access took minutes to run, the SQL View took parts of a second.

 

[mdlueck]

It is a subtle difference, did use this very example at first.
.

rrrr?????

Your code:

(SELECT     [COLOR=Blue]MIN(Req_Fin_Priority)[/COLOR] AS Expr1
FROM          dbo.Sub_1 AS Sub_1_1
WHERE      (dbo.Sub_1.ID_Wells = ID_Wells))

Should be able to be substituted with:

(SELECT [COLOR=Blue]TOP 1 Req_Fin_Priority[/COLOR] AS Expr1
FROM          dbo.Sub_1 AS Sub_1_1
WHERE      (dbo.Sub_1.ID_Wells = ID_Wells)
[COLOR=Blue]ORDER BY Req_Fin_Priority[/COLOR])

No?

 

[Rx_]

Pure Genius! Yes you are correct. Processed over a 100,000 records to evaluate - exactly the same result. Where were you this morining? LOL

Good use of the Order By and the Top. Glad this is posted. It will be good to review for the next time.

They are both so fast, there doesn't appear to be any difference in the speed or th output. Your example is more simple to understand. I have already created the necessary views and linked tables to my Access front-end.
Can you think of any reason for me to go back at this point and change it?
The tables are unlikely to grow from 100,000 to 300,000 over 5 years.

Thank you for pointing this out.

SELECT     ID_Wells, ID_SHLBHL, Req_Fin_Priority AS MinPriority, Req_Fin, SHLBHL
FROM         dbo.Sub_1
WHERE     (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))

 

[mdlueck]

Can you think of any reason for me to go back at this point and change it?

Well...

Your example is more simple to understand.

You seem to answer your own question!

 

[Rx_]

LOL
Thanks again.
Will be refering back to this post often.
And... using your code.
It has been a long day. I go home happy now with my finished product ready for use.