asked on

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>


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



<cfset networkRow = 1>

<cfoutput query="getNetworks">

<cfset currentNetwork = network>


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

rob_lorentz

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>

tags266

ASKER

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.

Myrandor

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.

JeffHowden

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>

ASKER CERTIFIED SOLUTION

RCorfman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial