keeping record order in a query

I have a stored procedure that returns a priority list of networks.  The two columns are computed_column_2, and network.  I'm taking the results of that stored procedure and using cfscript to rip it apart so i can use it within a cfquery select statement.  However when i do this it loses the order and my resutls are not in the order the stored procedure places them in.  how can i order by that list???


<cfscript>
  networks = ValueList(getChannels.network);
</cfscript>

<cfscript>
  priority = ValueList(getChannels.computed_column_2);
</cfscript>
<!--- if i output the variable here i get the correct order the results should be listed in --->

<cfquery name="getNetworks" datasource="people" maxrows="5">
select DISTINCT network
from dbo.networks
where networks.network IN (#ListQualify(networks, "'")#)
</cfquery>

<!--- once i output this query the list is not in the same priority order as it should be.. --->

<cfset networkRow = 1>

<cfoutput query="getNetworks">

  <cfset currentNetwork = network>

<!--- This query  narrows down the records by distinct network and by the selection of subregions the user picked --->
<cfquery name="getUnits" datasource="people" maxRows="1">
  SELECT *
  from dbo.units, dbo.subregions, dbo.networks, dbo.timeslots
where units.subregion_id = subregions.subregion_id
and units.network_id = networks.network_id
and units.timeslot_id = timeslots.timeslot_id
AND networks.network =  <cfqueryparam cfsqltype="cf_sql_varchar" value="#currentNetwork#">
and units.subregion_id IN (#form.subregion_id#)
</cfquery>
tags266Asked:
Who is Participating?
 
RCorfmanCommented:
When you do a DISTINCT in a query, it does an internal sort then strips unique values, and yes, it is going to re-determine the order of the records.

I'm still a little confused on what you are trying to do though... is the order of the computed column one the one that can be ordered by?  Using the ValueList function is giving me the impression that the GetChannels is already acting like a query.  If you can order by the computed_column_2, then you should be able to use a query of queries to output sorted by that, but I'm not sure I'm fully understanding...

If I understood correctly, run your getNetworks distinct query to get the networks, then run a query of queries to order the output....

<cfquery name="outputNetworks" dbtype="query">
select getNetworks.networks from
 getNetworks,getChannels
 where getNetworks.network=getChannels.network
 order by getChannels.computed_column_2
<cfquery>

Then, instead of using getNetworks as the outputing query for the rest of the page, use
OutputNetworks...

<cfoutput query="outputNetworks">

  <cfset currentNetwork = network>

<!--- This query  narrows down the records by distinct network and by the selection of subregions the user picked --->
..... etc, etc, etc......
0
 
rob_lorentzCommented:
you  can put an 'order by' clause on either one of your queries.

cfquery name="getNetworks" datasource="people" maxrows="5">
select DISTINCT network
from dbo.networks
where networks.network IN (#ListQualify(networks, "'")#)
order by network
</cfquery>
0
 
tags266Author Commented:
No, that's not it...if i order by network it gives me aphabetical.  My stored procedure returns networks in a specific order.  I set the variable 'networks' to the results of the stored procedure query.  If i output 'Networks' by itself and don't put an order by clause it gives it to me in the right order.  If i use order by networks in my SQL statement i get a db error.
0
 
MyrandorCommented:
In your stored procedures, you could create a new field which will contain the order of each field, and then use the "order by" in SQL to order it.

ColdFusion can have unexpected results when you don't use Order By. I think he mixed the field in an unwanted way.
0
 
JeffHowdenCommented:
I don't think that a query of any type is the answer here if the following query is what you're trying to accomplish:

<cfquery name="getNetworks" datasource="people" maxrows="5">
select DISTINCT network
from dbo.networks
where networks.network IN (#ListQualify(networks, "'")#)
</cfquery>

I'd just do a quick loop and grab the first five unique values dumping them into a query created on the fly:

<cfscript>
  getnetworks = QueryNew('network');
  networks = ListToArray(networks);
  last_network = '';
  for(i = 1; i LTE ArrayLen(networks); i = i + 1)
  {
    if(networks[i] NEQ last_network)
    {
      QueryAddRow(getnetworks);
      QuerySetCell(getnetworks, 'network', networks[i]);
      last_network = networks[i];
    }
    if(getnetworks.recordcount EQ 5)
      break;
  }
</cfscript>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.