I am trying to produce what I think should be a fairly straightforward cfquery and cfoutput of results, but just cannot seem to come up with the correct syntax. What I have is a database of records where I need to pull a list of restaurants, sorted alphabetically by business name, but with our sponsored links first (represented by a nonempty "Webaddress" field) in the list of search results. It needs to be a single query, I believe, as there are "n" records allowed per page, so I am utilizing RecordCount, MaxRows, and Previous/Next buttons. I believe that I need to use Group By in my query and/or Group in my cfoutput, or some combination thereof, but everything I try gives incorrect results, as well as issues with the Next and Previous records. So, here is an example of what I need:
A Restaurant (linked)
B Restaurant (linked)
C Restaurant (linked)
Next 3 Records >
D Restaurant (linked)
A Restaurant (no link)
B Restaurant (no link)
I hope the above makes sense.
Currently my query looks like this:
SELECT busname, Address, City, State, Zip, Phone1, Phone2, fax, email, Webaddress, pricerange, cuisine1, cuisine2, cuisine3, blurb
ORDER BY Webaddress, Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5)) ASC
My cfoutput looks like this:
<CFOUTPUT QUERY="results" StartRow="#StartRow#" maxRows="#MaxRows#">
My results are basically the opposite of what I need; i.e. unlinked results appear first, in alphabetical order, then the linked (sponsored) results appear second -- though these sponsored links are not in alphabetical order by the busname field, but rather the Webaddress field.
Any assistance someone could provide, to get these results the way I need them, would be SO appreciated. Thank you in advance!