Dynamic cfquery order

what you can do is setup a link with the url
<a href='filename?sort=Applicant'>Applicant</a>

And then in the query
 <cfquery name="Assignments" datasource="shs">
    SELECT COUNT(LEFT(status,1)) AS AlphaCnt, LEFT(status,1) AS Alpha
    FROM tblAssignment
    GROUP BY LEFT(status,1)
<cfif isDefined('url.sort') and url.sort eq 'Applicant'>
 ORDER BY LEFT(status,1) ASC
</cfif>
   
</cfquery>

That wont work because the status column may have a hundred "Closed" before the next status type like "inspected". Thus you would have to scroll all the way down which defeats the purpose. Ideally, I would like to click on the STATUS column header and it reload in the next avaialble begining character of the status after the initial page load.

or try this

wher status like '#url.variable#%'

@Dude, it's a little confusing what you're trying to accomplish.   Ordering is pretty much A-Z or Z-A.   It sounds like you're interested in filtering the contents so you will "hide" the status "Closed" or perhaps you will page-to the next status?   Are we really talking about sorting so that you have Closed at the bottom and Inspected at the top?   That's not an easily accomplished ordering, perhaps a filter or jump-to would work for you?

One thing I have done it to place a select box in the header column with all the possible status values, the user selects the desired status, the page redraws only showing records with that status...   ?

Or, instead of filtering to show only that status, you could jump to the page that that status starting...

Is it possible to pass a variable (Say the letter C) and order by where status values begin with 'C'? If I can order by a passed var then I think this will work.

I think you could move all the "C"  to the top and then just show the rest in alphabetical order.   Know what I mean?  If it doesn't begin with "C" then how does it order?

Your order by clause could be something like this...


   order by case when left(status,1) = 'C' then 1 else 2 end, status

That would push all "C" status to the top and then short everything else alphabetically

Cant use case right now.
Anyway to make this logically sound? the IIF statement is erroring.
<cfquery name="Assignments" datasource="shs">
        SELECT COUNT(LEFT(status,1)) AS AlphaCnt, LEFT(status,1) AS Alpha
        FROM assignments
        GROUP BY LEFT(status,1)
        order by IIF(left(status,1) = '#sort#',1,0), status
    </cfquery>

   iif() is a coldfusion function   your database will not understand it


   Whatever function you wrap around  status (a database column) needs to be understood by your database, not Coldfusion.


  I'm not thinking of a way to do it without case...  what's wrong with case?

I think because on the dev it's MS Crapcess
 Here is the error
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'case when left(status,1) = 'C' then 1 else 2 end'.

The full query:
<cfquery name="Assignments" datasource="shs">
        SELECT COUNT(LEFT(status,1)) AS AlphaCnt, LEFT(status,1) AS Alpha
        FROM Assignments
        GROUP BY LEFT(status,1)
        order by case when left(status,1) = '#sort#' then 1 else 2 end, status
    </cfquery>
I feel so close! Arghh!

I'm sorry, I didn't know it was MS access

MS access does have an  iif  function...


  not sure what is wrong with the iif  statement... does access require "  instead of single '  

 ?

Requires single '

I use another IIF statement in a sum in a cfquery. I think I have the wrong syntax for the order by with the IIF. Thoughts?

perhaps change the group by from

GROUP BY LEFT(status,1)

to

group by status


Your order by has 'status' in it so may cause a problem with the group...

You could also try removing "status" from the end of the order by as a test...

or trying the order by without grouping :)


what is the error message?

Ok, so this works.
<cfquery name="Assignments" datasource="shs">
        SELECT COUNT(LEFT(status,1)) AS AlphaCnt, LEFT(status,1) AS Alpha
        FROM Assignments
        GROUP BY LEFT(status,1)
        order by IIF(left(status,1) = '#sort#',1,0)
    </cfquery>

However, it doesnt give me the data only the first instance of the Sorted letter, say C for example.

Ok guys I'm reall close. The following works, However, If I choose "C" the first time I click the link, how can I tell it to go the next available letter in status? Here is the code...
<cfif isDefined('sort')>
    <cfquery name="GetStat" datasource="shs">
        SELECT distinct status
        FROM assignments
    </cfquery>
    *************************************************
    I think the solutions lies right here!
    <!---<cfif #sort# eq #getstat.status#></cfif>--->
    *************************************************
    <cfquery name="Assignments" datasource="shs">
        SELECT status
        FROM assignments
        order by IIF(status = '#sort#',1,0) DESC
    </cfquery>
<cfelse>
    <cfquery name="Assignments" datasource="shs">
        SELECT *
        FROM assignments
        Order by status asc
    </cfquery>
</cfif>

<cfoutput query="Assignments">
 <a href="selectBox.cfm?sort=#status#">#status#</a><br />
</cfoutput>

Ok, what if im using the whole status 'Closed', 'Cancelled', 'Inspected' and so on? Notice in my above query that it's sorting by the whole status string. Do I just drop the left() in the distinct query? If that snippet you just added did by the entire string then we've got it! I am so ready to move on to another task...

Right, so the Closed and Cancelled both starting with "C" are causing grief... is that right?

So I would think you could remove the left( ) function... but I would think you would need to remove it everyone including the order by clause, etc...

So from now on it will map against the entire word

Just when I think it's going to be the grand finale it stops working. I implemented that snippet and now it does nothing but display the same results set.

Here is the full code...
<cfif isDefined('sort')>
    <cfquery name="GetStat" datasource="shs">
        SELECT distinct status
        FROM assignments
    </cfquery>
    <cfset statusList = valueList(getStat.status)> <!--- build a list of all status codes ---->
      <cfset pos = listFindNoCase(statusList, sort)> <!--- find current sort on the list, is it 3rd, 2nd ? --->
      <cfif pos lt listLen(statusList)> <!--- if not the last item on the list, then grab the next one --->
        <cfset nextStatus = listGetAt(statusList,pos+1)>
      <cfelse> <!--- last status was picked, so grab the first one --->
        <cfset nextStatus = listFirst(statusList)>
      </cfif>
    <cfquery name="Assignments" datasource="shs">
        SELECT status
        FROM assignments
        order by IIF(status = '#nextStatus#',1,0) ASC
    </cfquery>
<cfelse>
    <cfquery name="Assignments" datasource="shs">
        SELECT *
        FROM assignments
        Order by status asc
    </cfquery>
</cfif>
<a href="selectBox.cfm?sort=#status#">Pick Next Status</a><br />
<cfoutput query="Assignments">
 #status#<br />
</cfoutput>

Sorry, wrong snippet... This code does not choose anything other than 'closed' or 'inspected'. No 'new', 'cancelled', etc.

<cfif isDefined('sort')>
    <cfquery name="GetStat" datasource="shs">
        SELECT distinct status
        FROM assignments
    </cfquery>
    <cfset statusList = valueList(getStat.status)> <!--- build a list of all status codes ---->
      <cfset pos = listFindNoCase(statusList, sort)> <!--- find current sort on the list, is it 3rd, 2nd ? --->
      <cfif pos lt listLen(statusList)> <!--- if not the last item on the list, then grab the next one --->
        <cfset nextStatus = listGetAt(statusList,pos+1)>
      <cfelse> <!--- last status was picked, so grab the first one --->
        <cfset nextStatus = listFirst(statusList)>
      </cfif>
    <cfquery name="Assignments" datasource="shs">
        SELECT status
        FROM assignments
        order by IIF(status = '#nextStatus#',1,0)
    </cfquery>
<cfelse>
    <cfquery name="Assignments" datasource="shs">
        SELECT *
        FROM assignments
        Order by status asc
    </cfquery>
</cfif>
<cfoutput>
<a href="selectBox.cfm?sort=#nextstatus#">Pick Next Status</a><br />
</cfoutput>
<cfoutput query="Assignments">
 #status#<br />
</cfoutput>

Got it!!! Here is the final solution...

<cfif isDefined('nextstatus')>
    <cfquery name="Assignments" datasource="shs">
        SELECT status
        FROM assignments
        order by IIF(status = '#nextstatus#',1,0) DESC
    </cfquery>
    <cfquery name="GetStat" datasource="shs">
        SELECT distinct status
        FROM assignments
    </cfquery>
    <cfset statusList = valueList(getStat.status)> <!--- build a list of all status codes ---->
    <cfset pos = listFindNoCase(statusList, nextstatus)> <!--- find current sort on the list, is it 3rd, 2nd ? --->
    <cfif pos lt listLen(statusList)> <!--- if not the last item on the list, then grab the next one --->
      <cfset nextStatus = listGetAt(statusList,pos+1)>
    <cfelse> <!--- last status was picked, so grab the first one --->
      <cfset nextStatus = listFirst(statusList)>
    </cfif>
<cfelse>
    <cfquery name="Assignments" datasource="shs">
        SELECT *
        FROM assignments
        Order by status asc
    </cfquery>
    <cfquery name="GetStat" datasource="shs">
        SELECT distinct status
        FROM assignments
    </cfquery>
    <cfset statusList = valueList(getStat.status)>
    <cfset nextStatus = listFirst(statusList)>
</cfif>
<cfoutput>
<a href="selectBox.cfm?nextstatus=#nextstatus#">Pick Next Status</a><br />
</cfoutput>
<cfoutput query="Assignments">
 #status#<br />
</cfoutput>

THANKS TO ALL, ESPECIALLY _AGX_!

excellent, glad you got it working !

 ... I didn't see _agx_ on this thread?