Sort Query after SQL

Hi

I dont want the query to be sorted alphabetically I want it to be sorted by my list.


I hope this explains better what I want to do:

I have a category list "e,c,b,d,a" and I have marked the items in the database with "cat1" which are the items that are in the list.

<cfquery datasource="database" name="test">
   SELECT itemname, itemcategory FROM table WHERE itemcategory = 'cat1'
</cfquery>

I have five items in the database where itemcategory="cat1", the itemnames are: a,b,c,d,e

After I have gotten my query I would like to display the query according to my list

If I could output the query according to my list then I would get a output like this:
e
c
b
d
a

The code which I gave you does not sort the query it sort them after they are stored in list.
What is the rule when you say according to my list. If itis desc then following code should do it.

 <cfquery datasoure="database" name="test">
   SELECT data  FROM table
</cfquery>


<cfset myList = ValueList(test.data)>
<p>Here is the unsorted list. </p>
<cfoutput>#myList#
   </cfoutput>
<p>Here is the list sorted alphabetically:</p>
<cfset sortedList = ListSort(myList, "Text","desc")>
<cfoutput>#sortedList#
   </cfoutput>

I know that the database contains the following items: a,b,c,d,e

I want to display the items after the query like this: e,c,b,d,a

and I want to able to does with my list "e,c,b,d,a".

If my list was like this for instance: "d,c,a,b,e"
then I would like to display the items after the query like this: d,c,a,b,e

Thats what I mean by according to the list.

You could loop through and do a manual comparison of the values before you output and keep a pointer in the list, but this is very computation intensive and can be slow for large things.

Basically you need to set up a query (may need to be SQL - I don't think Query of queries is that complex) and use the index of the position in the list and if it matches the position then add a custom column of the index and then sort by that.

to match your table at the top...

<cfset OrderList = "e,c,b,d,a">
<cfset qryOut=''>
     <cfloop list="#OrderList#" index="i" delimiters=",">
          <cfquery name="qry" datasource="test">
              SELECT data
              FROM table
              WHERE data = '#i#'
          </cfquery>
          <cfset qryOut=listappend(qryOut,ValueList(qry.data))>
     </cfloop>

<cfloop list="#qryOut#" index="Values">
     <cfoutput>#Values#<br></cfoutput>
</cfloop>

also a bit longer, but if you then also have values in your database you want to select with the letters you're ordering by a list you can do this...

<cfset OrderList = "e,c,b,d,a">
<cfset qryOut=''>
<cfloop list="#OrderList#" index="i" delimiters=",">
      <cfquery name="qry" datasource="test">
      SELECT date,FieldTwo
      FROM table
      WHERE date = '#i#'
      </cfquery>
      <cfoutput query="qry">
            <cfset qryOut=ListAppend(qryOut,date & '|' & FieldTwo)>
      </cfoutput>
</cfloop>
<table>
      <tr><td>Column One</td><td>Column Two</td></tr>
        <cfloop list="#qryOut#" index="Values">
            <tr>
                  <cfoutput><td align="center">#listGetAt(Values,1,'|')#</td><td align="center">#listGetAt(Values,2,'|')#</td></cfoutput>
            </tr>
        </cfloop>
</table>

Yes, but what you are doing is putting a query inside a loop. It is easier your way, but also alot worse on performance, especially with larger tables and larger data.

Yes it would be slower if there were a large number of records, but what can you expect from this type of sort?

How much faster does, querying > building a query off the results of the query w/loop> then querying that query.. over just looping over a query a small number of times regardless of records returned?

you may find the performance similar if not faster in basically looping for your result in my example, my example only loops for the number of elements in the list to sort by, it doesn't loop from every row returned, yours does however... how would that compare to your example?

In my last example it does output the query result into a list, this would be the slowest portion, however yours performs a similar function when building a query...

I'm just curious how they'd match up performance wise =) I'll give it a test...

~trail

i found a list index error here...

     <cfloop query='variables.test'>
          <cfif variables.test.data is listgetat(orderby, variables.sortcount)> <----- LIST INDEX ERROR WHEN LIST REACHES 6
               <cfset temp = querysetcell(newquery, 'data2', variables.test.data, variables.sortcount)>
          </cfif>
     </cfloop>

you also don't need to include query name within a <cfoutput query> and you don't need to prepend "variables" to every variable, just some helpful tips ;o)

~trail

Friends,
There are always many way to code things.
We can also use Query of query instead of query in the loop and this will be not slower. There are always different way to code things. It all depends on database type, query complexity, table column and row numbers. For small data many things does not matter where as if there are large data simple order by statement can slow the database down.
Please keep the goal simple to help the asker and learn something new for ourselves. We are here as a team.

something like USING TRAIL'S EXAMPLE
  <cfquery name="qry1" datasource="testDB">
     SELECT data
     FROM Struct
     </cfquery>

<cfset OrderList = "e,c,b,d,a">
<cfset qryOut=''>
<cfloop list="#OrderList#" index="i" delimiters=",">
     <cfquery name="qry" dbtype="query">
     SELECT data
     FROM qry1
     WHERE data = '#i#'
     </cfquery>
     <cfset qryOut=listappend(qryOut,ValueList(qry.data))>
</cfloop>
<cfloop list="#qryOut#" index="Values">
<cfoutput>#Values#<br></cfoutput>
</cfloop>

pinaldave

Thx andw928, trailblazzyr55 and pinaldave.

I tested andw928 and trailblazzyr55 scripts and they both worked fine.

I had already done a script similar to trailblazzyr55 but I didn't like it because I didn't want to do many querys on the database, I was hoping to do 1 query and then sort it out. But andw928 script, which is excellent, seems to be just as heavy as to doing a query for every single item I need.

There is probably no simple solution to this task. Its always going to be heavy for the server.

I have a gut feeling that trailblazzyr55 answer is going to cause less load on the server, but I can't be sure.

The data I'm working with will never exceed 1000 records, from these 1000 records there will be about 10 and I always know the order they are in, so thats 10 querys on 1000 records.

What do you think guys? Which is less heavy?

I was looking at the MYSQl forum on EE and I found this solution also:

SELECT *, FIND_IN_SET(data, 'e,c,b,d,a') as sortColumn FROM table ORDER BY sortColumn;

Im not that advanced in MySql and didnt think/know that MySql could solve this, but it sure does.