bubble or LIFO or some sort of recordset sort

Hi Experts,

I was going along smoothly until I hit a rut. I've got the SQL guys giving me some SQL code, but this will require a huge rewrite of a CFC and reduce overall application efficiency.

I'd like to sort a recordset BEFORE outputting it. This cannot be done with SQL as I'm using a QoQ and it lacks any functions to handle such a task.

Here is my query

select *
from tbl_myTable
where tableID in (342,34,532,24,463,14,12,4,643)

I need that order preserved, but of course, since they are IDs, they are returned as a numerical sort and my list order is lost.

I tried to do a bubble sort (I think if I can remember proper terms from the college days) but it took forever.

->copy current pointer row into temp struct, find correct row and move to current, replace found row with tempstruct data.
Maybe this was sloppy, but it took 2.5 seconds to run.

What I need to do is basically reorder those query results back to the original list order. Any high performance suggestions?
Who is Participating?
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.

I'm a bit confuesd on how you want to sort?

you have this..where tableID in (342,34,532,24,463,14,12,4,643)

so you're saying you want the output to sort in this order...  "342,34,532,24,463,14,12,4,643" ?

I'm just not sure how you want to order your output, is there other columns ur outputing? what's the original order?
jtreherAuthor Commented:
Hey Trail,

I do indeed want it to remain sorted by the order of the IN list, however, it is currently returning them in numerical order - 4,12,1,24,34,342,463,532,643 as the default query behavior.

So - I'm feeding this query a list of IDs ordered as I want and it is giving me back the list reordered numerically. Therefore, I need to sort them before I can output them. Indeed, this is a master-detail and all I have are sorted IDs which will give me access to a slew of details, but I need those rows to stay correctly ordered!

You can do this....

<cfset orderList="342,34,532,24,463,14,12,4,643">
<cfset Values=''>
<cfloop list="#orderList#" index="i">
<cfquery name="qry" datasource="testDB">
select *
from tbl_myTable
WHERE tableID IN(#i#)
<cfset Values=ListAppend(Values,"#qry.tableID#~#qry.ColumnOne#~#qry.ColumnTwo#~#qry.ColumnThree#")>

<cfloop from="1" to="#ListLen(orderList)#" index="j">
      <cfset ListValue=ListGetAt(Values,j)>
      <cfoutput>#j#: </cfoutput>
      <cfloop from="1" to="#ListLen(ListValue,'~')#" index="idx">

yes this isn't all within the query... but it works and probably faster than the way you may be doing it now... maybe =)

give that a try and test the performance
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

jtreherAuthor Commented:

So am I to take it that it is impossiible to sort a recordset post query?

Here is a big catch...I only want to this to run as one option. Sometimes I will be using an order by statement.

In case you are wondering, yes, this is part of my search engine and the list contains IDs from the search tag. I need the order of relevance preserved, which it isn't, but I caught before production. However, sometimes I will order by date, title, DESC or ASC and that works just peachy, performance is right on. I just never caught that my 'relevance' list was being reordered.

The thing about using loops is that could be a lot of queries, almost 500 for some searches.
>>So am I to take it that it is impossiible to sort a recordset post query?

But without adding a sort field I don't know of a way to sort based on the IN order - esepcially since records are not returned in that order - without manually doing so.

You could do a loop over the query results and add into a new manual query in the order you desire, but that would be slow too..
If on SQL2000 you can use a CURSOR to loop over the string and add the records to the recordset including an itteration index.

Or you could use the following the following code to loop over the list

-- Author: Taco Fleur (taco@coldfusionist.com)
-- Function: duplicates coldfusions cfloop list functionality
      @list VARCHAR(8000),
      @delimiter VARCHAR(20) = ','

      DECLARE @startIndex SMALLINT, @endIndex SMALLINT, @value VARCHAR(100)

      IF NOT (RIGHT(@list, 1) = @delimiter)
            SET @list = @list + @delimiter
      SET @startIndex = 1
      SET @endIndex = 1
      WHILE @endIndex > 0
            SET @endIndex = CHARINDEX(@delimiter, @list, @startIndex)
            IF @endIndex > 0
                  SET @value = SUBSTRING(@list, @startIndex, @endIndex - @startIndex)
                  -- only insert value if not empty
                  IF LEN(@value) > 0
                        INSERT INTO @tempTable VALUES (@value)
                  SET @startIndex = @endIndex + 1

it should return a table with the values and the row number, you should be able to perform a join on this.

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
sorry, perform a join on rowValue and sort by the row number - make sense?
jtreherAuthor Commented:
Tacobell - Well, the goal was to not go back to server because I already have a query cached in memory that basically needs a partial pull and an ordering of data.

I have a good strored proc from the SQL experts and I've even got a very structured XML bit, but I'd much rather resort the recordset if possible.

Sorry about the delay, I was fortunately without access this weekend.

Mrichmon - it seems that a second query is about as slow as repointing the recordset with a bubble sort. Doh! Too many loops.

Trail - That query option was pretty slow as well.

The big issue is that I have a lot happening with the cached query, so it runs once for 600ms every hour; I simply can't go back to the SQL server everytime someone does a search. I'm going to look at an output loop option to see if that will work...

I've got an unordered recordset with IDs. I have a list of the IDs in the correct order, something fast should be simple!
jtreherAuthor Commented:
Just tried two for loops

one looped through list, the other the query to look for matching ids and build a temporary in memory table with querysetcell.

This took 1 second for max results - too long.

A storedproc might be my only way.
Why can't you put the order on that query that runs before you cache it?

PS. 600ms is not even a second so why are you worried?
jtreherAuthor Commented:
Well, the 600ms could be hit multiple times per second, I'm worred about a queue building and crashing the server.

This list will change nearly every time the query is processed, so caching it would be pointless with an order by.

I think what I'm going to try is to build a temporary in memory table with with coldfusion or use a sql function and then do a join with a sort on the id field of the temp table.

This method most recommended by SQL people and you Tacobell.

I'll let you know how I fare!
jtreherAuthor Commented:
Good call with the function.

I just created a recordset with the function, giving me access to the temp table as a join in my original query of the cached query.

Total performance cost: less than 10 millis on average!
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.