coldfusioncoldfusion-9coldfusion-10

Query sort in coldfusion

I have a group of data from excel file read the file and take the result in a variable for create a new excel with what are wrong and i try

local.sheetData =queryNew("FirstName,LastName,Address,Email,Phone,DOB,Role");
        for(row in data){
            queryAddRow(local.sheetData);
         
            if(row["First Name"] != '' || row["Last Name"] != '' || row["Address"] != '' || row["Email"] != '' || row["Phone"] != '' || row["DOB"] != '' || row["Role"] != ''){
                if(row["First Name"] === '' || row["Last Name"] === '' || row["Address"] === '' || row["Email"] === '' || row["Phone"] === '' || row["DOB"] === '' || row["Role"] === ''){

                    local.endresult = arrayToList(local.nullMessage)
                    querySetCell(local.sheetData, "FirstName", row["First Name"]);
                    querySetCell(local.sheetData, "LastName", row["Last Name"]);
                    querySetCell(local.sheetData, "Address", row["Address"]);
                    querySetCell(local.sheetData, "Email", row["Email"]);
                    querySetCell(local.sheetData, "Phone", row["Phone"]);
                    querySetCell(local.sheetData, "DOB", row["DOB"]);
                    querySetCell(local.sheetData, "Role", row["Role"]);
                   
                }else{
                    querySetCell(local.sheetData, "FirstName", row["First Name"]);
                    querySetCell(local.sheetData, "LastName", row["Last Name"]);
                    querySetCell(local.sheetData, "Address", row["Address"]);
                    querySetCell(local.sheetData, "Email", row["Email"]);
                    querySetCell(local.sheetData, "Phone", row["Phone"]);
                    querySetCell(local.sheetData, "DOB", row["DOB"]);
                    querySetCell(local.sheetData, "Role", row["Role"]);
                    
                }
            } 
        }
        
       return local.sheetData;

it working fine and get the result like

enter image description here

i wand sort this query and show the any cell is empty that row show first. how to sort the query in coldfusion ?

Solution

  • The easiest way to sort a query in ColdFusion is to use the Query-of-Queries feature.

    tag syntax

    <cfset myQuery = ...>
<cfquery name="sortedQuery" dbtype="query">
  SELECT * FROM myQuery ORDER BY col1, col2 DESC;
</cfquery>

    cfscript syntax

    var myQuery = ...;
var sortedQuery = queryExecute("
   SELECT * FROM myQuery ORDER BY col1, col2 DESC;
", {}, {dbtype: "query"});

    In any case - if you insert rows in the right order, you don't need to sort anything, so pre-sorting data before your loop would be the most efficient option you have.