tam423
asked on
Need cfquery output results grouped alphabetically and sorted by sponsored links at top
Hello Experts,
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:
Page 1:
A Restaurant (linked)
B Restaurant (linked)
C Restaurant (linked)
Next 3 Records >
Page 2:
D Restaurant (linked)
A Restaurant (no link)
B Restaurant (no link)
...etc.
I hope the above makes sense.
Currently my query looks like this:
<CFQUERY DATASOURCE="mwinfo"
NAME="results">
SELECT busname, Address, City, State, Zip, Phone1, Phone2, fax, email, Webaddress, pricerange, cuisine1, cuisine2, cuisine3, blurb
FROM dining
ORDER BY Webaddress, Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5
</CFQUERY>
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!
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:
Page 1:
A Restaurant (linked)
B Restaurant (linked)
C Restaurant (linked)
Next 3 Records >
Page 2:
D Restaurant (linked)
A Restaurant (no link)
B Restaurant (no link)
...etc.
I hope the above makes sense.
Currently my query looks like this:
<CFQUERY DATASOURCE="mwinfo"
NAME="results">
SELECT busname, Address, City, State, Zip, Phone1, Phone2, fax, email, Webaddress, pricerange, cuisine1, cuisine2, cuisine3, blurb
FROM dining
ORDER BY Webaddress, Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5
</CFQUERY>
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!
ASKER
qdemaria,
Thanks so much for the quick response. Can you clarify what the actual ORDER BY clause would be, to account for the NULL Webaddres, etc.?
Thanks so much for the quick response. Can you clarify what the actual ORDER BY clause would be, to account for the NULL Webaddres, etc.?
You didn't mention, but it looks like you are using MySQL, so the syntax would be a bit different..
<CFQUERY DATASOURCE="mwinfo" NAME="results">
SELECT busname, Address, City, State, Zip, Phone1, Phone2, fax, email, Webaddress, pricerange, cuisine1, cuisine2, cuisine3, blurb
FROM dining
ORDER BY iif(Webaddress is null, 2, 1), Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5
</CFQUERY>
<CFQUERY DATASOURCE="mwinfo" NAME="results">
SELECT busname, Address, City, State, Zip, Phone1, Phone2, fax, email, Webaddress, pricerange, cuisine1, cuisine2, cuisine3, blurb
FROM dining
ORDER BY iif(Webaddress is null, 2, 1), Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5
</CFQUERY>
> Can you clarify what the actual ORDER BY clause would be, to account for the NULL Webaddres
if the webaddress is null, then assign a "2" to that record, if it has a value, then assign a 1 to that record
Sorting by this, will place all webaddress records first, then all non-webaddress records
Then sort by business name
1 AAAA
1 CCC
1 DDD
2 BBBB
2 EEEE
2 FFFF
if the webaddress is null, then assign a "2" to that record, if it has a value, then assign a 1 to that record
Sorting by this, will place all webaddress records first, then all non-webaddress records
Then sort by business name
1 AAAA
1 CCC
1 DDD
2 BBBB
2 EEEE
2 FFFF
ASKER
qdemaria,
I am actually using MS Access. I tried your syntax above and the results are better, but still not correct. What I'm getting is something like this:
Page 1:
A Restaurant (linked)
B Restaurant (linked)
C Restaurant (no link)
D Restaurant (linked)
E Restaurant (linked)
F Restaurant (no link)
G Restaurant (no link)
H Restaurant (linked)
Page 2:
R Restaurant (no link)
S Restaurant (linked)
...
Y Restaurant (linked)
Z Restaurant (no link)
Page 3:
A thru Z Restaurants (no link)
So, while most of the sponsored links are now sorted first, there are a few non-sponsored listings being interspersed among them, for some reason (and those are not repeated in the A thru Z non-sponsored listings coming at the end). I verified in the database that there is, indeed, nothing in the Webaddress field for those interspersed ones, as well. I don't know why this is happening -- could it be because the synatax should be something else, for MS Access vs. MySQL? Thank you again...
I am actually using MS Access. I tried your syntax above and the results are better, but still not correct. What I'm getting is something like this:
Page 1:
A Restaurant (linked)
B Restaurant (linked)
C Restaurant (no link)
D Restaurant (linked)
E Restaurant (linked)
F Restaurant (no link)
G Restaurant (no link)
H Restaurant (linked)
Page 2:
R Restaurant (no link)
S Restaurant (linked)
...
Y Restaurant (linked)
Z Restaurant (no link)
Page 3:
A thru Z Restaurants (no link)
So, while most of the sponsored links are now sorted first, there are a few non-sponsored listings being interspersed among them, for some reason (and those are not repeated in the A thru Z non-sponsored listings coming at the end). I verified in the database that there is, indeed, nothing in the Webaddress field for those interspersed ones, as well. I don't know why this is happening -- could it be because the synatax should be something else, for MS Access vs. MySQL? Thank you again...
Let's try a bit different, perhaps the field cotains an empty string rather than NULL
ORDER BY iif(isNull(Webaddress,'') = '', 2, 1), Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5
also try this to see what the field holds...
SELECT if(webaddress is null,'Null', if (webaddress = '','Empty', 'Populated'))
from table...
ASKER
gdemaria,
I tried your new ORDER BY syntax, but got the error,
"Wrong number of arguments used with function in query expression 'iif(isNull(Webaddress,'')
I also tried your SELECT statement in a test query to see what the field holds:
<CFQUERY DATASOURCE="mwinfo"
NAME="results">
SELECT busname, Address, City, State, Zip, Phone1, Phone2, fax, email, if(webaddress is null,'Null', if (webaddress = '','Empty', 'Populated')), pricerange, cuisine1, cuisine2, cuisine3, blurb
FROM dining
ORDER BY Webaddress, Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5
</CFQUERY>
...and received the error,
"Undefined function 'if' in expression. "
So, I probably don't have the SELECT query correct, in some way. I'm sorry -- a little out of my element here...
I tried your new ORDER BY syntax, but got the error,
"Wrong number of arguments used with function in query expression 'iif(isNull(Webaddress,'')
I also tried your SELECT statement in a test query to see what the field holds:
<CFQUERY DATASOURCE="mwinfo"
NAME="results">
SELECT busname, Address, City, State, Zip, Phone1, Phone2, fax, email, if(webaddress is null,'Null', if (webaddress = '','Empty', 'Populated')), pricerange, cuisine1, cuisine2, cuisine3, blurb
FROM dining
ORDER BY Webaddress, Iif(INSTR(UCASE(busname), 'THE ')=0,busname,MID(busname,5
</CFQUERY>
...and received the error,
"Undefined function 'if' in expression. "
So, I probably don't have the SELECT query correct, in some way. I'm sorry -- a little out of my element here...
no points ...
Unfortunately MS Access does many things differently than in most db's :) So things that work one way in MS SQL have to be tweaked for MS Access. I'm pretty sure ISNULL is one of those functions.
isNull(Webaddress,'')
In MS SQL that says "Webaddress" is null, return an empty string instead. I don't know the equivalent function in Access. So I'd fall back to using IIF
IIF( Webaddress IS NULL, '', Webaddress)
Unfortunately MS Access does many things differently than in most db's :) So things that work one way in MS SQL have to be tweaked for MS Access. I'm pretty sure ISNULL is one of those functions.
isNull(Webaddress,'')
In MS SQL that says "Webaddress" is null, return an empty string instead. I don't know the equivalent function in Access. So I'd fall back to using IIF
IIF( Webaddress IS NULL, '', Webaddress)
Typo correction: In MS SQL that says IF "Webaddress" is null, THEN return an empty string instead.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
gdemaria,
That was it! Thanks so much! I just had to change your "if" to "iif" in the second part of your ORDER BY clause -- (iif(webaddress='',2,1) -- and your latest solution worked like a charm.
Thank you again!!
That was it! Thanks so much! I just had to change your "if" to "iif" in the second part of your ORDER BY clause -- (iif(webaddress='',2,1) -- and your latest solution worked like a charm.
Thank you again!!
<CFQUERY DATASOURCE="mwinfo" NAME="results">
SELECT busname, Address, City, State, Zip, Phone1, Phone2, fax, email, Webaddress, pricerange, cuisine1, cuisine2, cuisine3, blurb
FROM dining
ORDER BY case when Webaddress is null then 2 else 1 end, busname
</CFQUERY>