469,306 Members | 1,901 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

Query help - MIN

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
2 1839
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
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by jaysonsch | last post: by
9 posts views Thread by netpurpose | last post: by
3 posts views Thread by Harvey | last post: by
4 posts views Thread by Diamondback | last post: by
14 posts views Thread by Dave Thomas | last post: by
4 posts views Thread by Stan | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.