September 10, 2004 at 9:47 pm
RowID Priority Value
111 0 8
112 0 32
113 1 13
114 2 118
I want a subquery to test for the lowest "priority" row whose "value" meets the condition >15, and return the RowID
September 10, 2004 at 11:07 pm
I didn't test this, but..
SELECT ActualTable.*
FROM ActualTable
INNER JOIN
(SELECT Min(Priority) AS Priority
FROM ActualTable
WHERE Value > 15) MinPriority
ON ActualTable.Priority = MinPriority.Priority
Note that this will return all records who have the minimum Priority that have a value greater than 15 (which in this case happens to be one record).
Everett Wilson
ewilson10@yahoo.com
September 11, 2004 at 7:48 am
I guess I tried to oversimplify the problem too much.
There is an outer query containing about 10 tables joined. For each record in one of the joined tables, say "MasterTable", I want to see if any rows from a related (by "GroupID") table will match a set of criteria, say we check its "Value", and of those matching, I want the one with the lowest "Priority", or to ignore the entire row altogether.
MasterTable (MT)
MTID GroupID
0001 1000
0002 1001
SlaveTable (ST)
STID GroupID GradeID Priority Value
0087 1000 X 0 87
0088 1000 Y 2 12
0089 1000 Z 1 40
0090 1001 A 0 124
0091 1001 A 0 76
0092 1001 B 1 850
so lets say the value I want to match is <50
so, for the MasterTable, I would be reading the first row (0001), then try to return the GradeID whose GroupID matches (down to records 0087,0088,0089) and whose value is less than 50 (down to records 0088,0089) and now I'll take the first record available that has the lowest priority (0089)
Here is my attempt:
select ..... MT.* .....from ..... join MasterTable MT .... join (select TOP 1 GradeID,GroupID from SlaveTable Where Value<50 ORDER BY Priority ASC ) ST on MT.GroupID=ST.GroupID
The problem above was that the inner select evaluated before I could use the constraint limiting it to the rows matching the outer table, but I can't do the constraint on the inner table table either, because it doesnt recognize the outer table
September 13, 2004 at 9:53 am
Ouch. The problem being that you really don't want to stick the 10 joined tables into the subquery in order to find one lousy data point. I don't know any other way to do this in the SQL (someone else might).
What I can suggest is comparing performance of sticking the whole mess into the subquery versus using a temporary table (build master, determine add'l data and update master).
Everett Wilson
ewilson10@yahoo.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply