[thelist] CF - multiple groups, and SQL too perhaps

>(rudy, how bad is my SQL?)

it's fine

>now, 2 level is easy:

yup

  <cfoutput query=getreviews group=auctionid>
    <cfoutput>
    </cfoutput>
  </cfoutput>

>but what about 3 level?

good question, i never needed this yet

> should i be using "group by location.locationid"
> within my sql? something like that?

not unless you want only one result record per locationid  ;o)

okay, here goes...

have the outer cfoutput detect "group" changes on the *composite* key of
location plus auction

obviously this is gonna be somewhat of a hack, but you create a single
column in the sql consisting of the concatenation of the location and
auction fields

make that column your group parameter in the cfoutput, and
then do a cfif to detect when the location (single) column changes

and dude, don't write select *, you'll thank me later

okay,

  select locationname & auctiontitle as locationauction
     , locationname, auctiontitle
     , pluswhateverothercolumnsyouneed
   [snip]
  order by locationauction, reviewdate

note ampersand is concatentation in this example, your syntax may vary

also note the order by can be on the individual columns locationname,
auctiontitle instead of the concatenated column (do you see why?) but that
would be obfuscation, if you're gonna group in a cfoutput, have it as the
highest sort key

then

  <cfset lastloc="">
  <cfoutput query=getreviews group=locationauction>
  <cfif locationname is lastloc>
  <cfelse> output the new location data
  <cfset lastloc = locationname>
  </cfif>
   now output the auction data
    <cfoutput>
       this is where the review stuff gets output
    </cfoutput>
  </cfoutput>

warning: untested

please let me know if this works out, i may need this some day too  ;o)

_____________
rudy limeback
http://r937.com/
http://evolt.org/