[thelist] Custom sorting/ordering of query results

[Luther, Ron]

Frank Marion asked:

>>...the client wants it custom sorted

>>Is there a way of getting the result to match a certain order without 
>>resorting to looping over a query several times? 


Hi Frank!

Maybe.  Suppose the client maintained a small file 'someplace' where 
you could get at it. If they always sorted on the same field it could 
be something simple like:

Value_in_Data_To_Sort        Sort_Order
---------------------        ----------
    Cameras                      1
    MP2 Players                  3
Cockney 'ockey 'ats              2

[If the client wanted an arbitrary field, the structure would be a 
little more complex.]

Some DBs and report writers allow you to pull that file directly into 
your SQL data model and use the client's sort order as if it were a 
DB table field.  That gives you one query to execute with results 
returned in the order specified by the user.

If you don't trust the user to specify all possible values you may 
want to use a 'right join' to their manual table.


{The following example may not be very useful to you, since it uses 
JavaScript inside a report writer package called "Brio", but it is a 
working example where I add an Excel file sitting on an end user's 
hard drive to the SQL I send to a corporate Oracle database. In 
this case I'm just adding some computed fields to the select 
statement, but I could have used the spreadsheet for the sort order 
as well:}
http://www.fangsnclaws.com/Brio/brio_tip15.html



HTH,

RonL.