?
Solved

keeping record order in a query

Posted on 2006-04-12
6
Medium Priority
?
217 Views
Last Modified: 2013-12-24
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>
0
Comment
Question by:tags266
5 Comments
 
LVL 10

Expert Comment

by:rob_lorentz
ID: 16439928
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
 

Author Comment

by:tags266
ID: 16440140
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
 
LVL 4

Expert Comment

by:Myrandor
ID: 16440799
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
 
LVL 7

Expert Comment

by:JeffHowden
ID: 16442539
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
 
LVL 16

Accepted Solution

by:
RCorfman earned 2000 total points
ID: 16462172
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question