Query help - MIN

Todd

Apologies up front if this is completely confusing. Currently, I'm
working on a query that resembles this:

Select A, B, C, MIND, E2.E, F2.F
From (Select A, B, C, MIN(D) As MIND
From TableABC ABC
,TableD D
,TableE E1
,TableF F1
,etc.
Where ABC.A = D.PkeyCol1of2 (note: 'D' is PkeyCol2of2)
and D.fkey = E1.pkey
and E1.fkey = F1.pkey
and E meets condition1
and F meets condition2
and etc.
Group By A, B, C) as ID

Join TableD
On PkeyCol1of2 = ID.A
And D = MIND
Join TableE E2
On Dfkey = E2.pkey
Join TableF F2
On Efkey = F2.Fpkey

I'm trying to determine if there is a better way to write this query.
D is a sequence number that I need the MINimum of the numbers that
match the rest of the queries conditions. TableABC is a one to many
relationship to D, While D to E and E to F are both one to one. The
columns from E and F, which are specific to each row from D are
necessary in determining which rows from D meet the queries overall
criteria, but I can't 'group by' them to get the Minimum, so I'm
forced to have to Join these tables a second time outside of getting
the Minimum, this strikes me as overkill and leads me to think there
may be a way to restructure to avoid having to go after the data
again, when its already available at the time of the evaluation of the
subselect. The conditions within the inline view above are actually
much more lengthy and involve more tables than the 2 conditions "E
meets condition1" and "F meets condition2" would appear to indicate
which caused any attempt to instead obtain the MIN within a subselect
as a condition of a single query to have to repeat the entire query
within that MIN subquery, just to get the appropriate rows to select
the MIN from. This also struck me as overkill. Because of my
self-proclaimed amateur sql writing status, I considered it possible
that I was missing a more obvious method. Hopefully the way I
explained above was somewhat clear as to the problem. Let me know if
I can clarify any items.

Thanks

tc

Nov 12 '05 #1

Tokunaga T.

How about this?
(I asumed D in MIN(D) is PkeyCol2of2.)
WITH S1 AS (
SELECT A, B, C, PkeyCol2of2, E1.E, F1.F
, ROWNUMBER() OVER(PARTITION BY A, B, C ORDER BY PkeyCol2of2 ASC) AS rnum
FROM TableABC ABC
, TableD D
, TableE E1
, TableF F1
, etc.
WHERE ABC.A = D.PkeyCol1of2
AND D.fkey = E1.pkey
AND E1.fkey = F1.pkey
AND E meets condition1
AND F meets condition2
AND etc.
)
SELECT A, B, C, PkeyCol2of2, E, F
FROM S1
WHERE rnum = 1
;

Also, you can make S1 as nested table expression.

Nov 12 '05 #2

Tokunaga T.

It may be not necessary to include B and C in PARTITION BY of ROWNUMBER.

WITH S1 AS (
SELECT A, B, C, PkeyCol2of2, E1.E, F1.F
, ROWNUMBER() OVER(PARTITION BY A ORDER BY PkeyCol2of2 ASC) AS rnum
FROM TableABC ABC
, TableD D
, TableE E1
, TableF F1
, etc.
WHERE ABC.A = D.PkeyCol1of2
AND D.fkey = E1.pkey
AND E1.fkey = F1.pkey
AND E meets condition1
AND F meets condition2
AND etc.
)
SELECT A, B, C, PkeyCol2of2, E, F
FROM S1
WHERE rnum = 1
;

Nov 12 '05 #3