RE: using a dynamic value in aggregate function...

# Thread: RE: using a dynamic value in aggregate function...

1. Join Date
Dec 1969
Posts
44

## RE: using a dynamic value in aggregate function...

I&#039;m wondering how I would get the Max function to run where for example:<BR> Max( CASE WHEN jb2.conductororpairnumber= Min(jb2.conductororpairnumber) THEN ...<BR>I know this cannot work b/c an aggregate function cannot contain another aggregate function but is there another way to achieve this? The problem is that the minimum conductororpairnumber will not always be = 1.<BR><BR><BR>Here is the full query.<BR><BR>SELECT m.rack, m.slot, m.point,<BR>Min(jb1.conductororpairnumber) AS jb1tb1,<BR>Max(jb1.conductororpairnumber) AS jb1tb2,<BR>Max( CASE WHEN jb2.conductororpairnumber=1 THEN jb2.conductororpairnumber ELSE NULL END ) as jb2tb1, <BR>Max( CASE WHEN jb2.conductororpairnumber=2 THEN jb2.conductororpairnumber ELSE NULL END ) as jb2tb2, <BR>Max( CASE WHEN jb2.conductororpairnumber=3 THEN jb2.conductororpairnumber ELSE NULL END ) as jb2tb3, <BR>Max( CASE WHEN jb2.conductororpairnumber=4 THEN jb2.conductororpairnumber ELSE NULL END ) as jb2tb4, <BR>Max( CASE WHEN jb2.conductororpairnumber=5 THEN jb2.conductororpairnumber ELSE NULL END ) as jb2tb5, <BR>Max( CASE WHEN jb2.conductororpairnumber=6 THEN jb2.conductororpairnumber ELSE NULL END ) as jb2tb6, <BR>Max( CASE WHEN jb2.conductororpairnumber=7 THEN jb2.conductororpairnumber ELSE NULL END ) as jb2tb7, <BR>Max( CASE WHEN jb2.conductororpairnumber=8 THEN jb2.conductororpairnumber ELSE NULL END ) as jb2tb8, <BR>Max( CASE WHEN jb2.conductororpairnumber=9 THEN jb2.conductororpairnumber ELSE NULL END ) as jb2tb9, <BR>Max( CASE WHEN jb2.conductororpairnumber=10 THEN jb2.conductororpairnumber ELSE NULL END ) as jb2tb10, <BR>Max( CASE WHEN jb2.conductororpairnumber=1 THEN IsNull(gi.newtagnumber,&#039;&#039;) ELSE &#039;&#039; END ) as jb2instr1tag,<BR>Max( CASE WHEN jb2.conductororpairnumber=3 THEN IsNull(gi.newtagnumber,&#039;&#039;) ELSE &#039;&#039; END ) as jb2instr2tag,<BR>Max( CASE WHEN jb2.conductororpairnumber=5 THEN IsNull(gi.newtagnumber,&#039;&#039;) ELSE &#039;&#039; END ) as jb2instr3tag,<BR>Max( CASE WHEN jb2.conductororpairnumber=7 THEN IsNull(gi.newtagnumber,&#039;&#039;) ELSE &#039;&#039; END ) as jb2instr4tag,<BR>Max( CASE WHEN jb2.conductororpairnumber=9 THEN IsNull(gi.newtagnumber,&#039;&#039;) ELSE &#039;&#039; END ) as jb2instr5tag, <BR>jb1.name, jb2.name as instrjbname<BR>FROM junctionbox AS jb1, module AS m, junctionbox AS jb2, cable AS c, generalinstruments AS gi<BR>WHERE m.parentcableid = jb1.frcableid AND m.ConductorOrPairNumber = jb1.ConductorOrPairNumber AND<BR>jb1.tocableid = jb2.tocableparentid AND<BR>jb2.tocableid = c.cableid AND<BR>gi.cableid = c.cableid AND<BR>gi.notenumber = 1<BR>GROUP BY m.rack, m.slot, m.point, jb1.name, jb2.name<BR>ORDER BY m.rack, m.slot, m.point<BR><BR><BR>Thanks for any help

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Could do it in a Stored Proc....

...but if you don&#039;t want to tackle a SP, then I&#039;d just do a pre-query to get that minimum value and then use it to build the main SQL query.<BR><BR>Actually, even if you used a SP that&#039;s how you&#039;d want to do it, most likely.<BR><BR>You *could* JOIN to a view that gets the MIN of course, but I doubt that the performance would be better and it just makes things more complex to code.<BR><BR>

3. Join Date
Dec 1969
Posts
44

## RE: what i ended up doing was...

Creating a temporary table and then joining to it in order to access this number. I don&#039;t know if this is the best method or not but the code was easy and doesn&#039;t seem to be too costly in terms of performance (at least for the size of my database).<BR><BR>Thanks for the ideas though.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•