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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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>
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.
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.
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>
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......

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.