[thelist] Sorting with SQL

Joshua Olson joshua at waetech.com
Thu Sep 25 09:36:33 CDT 2003

----- Original Message ----- 
From: "Rob Smith" <rob.smith at THERMON.com>
Sent: Thursday, September 25, 2003 10:00 AM

> ...Yah.  The known patterns are:
>   LLL N-N
>   LLL NN-N
>   LL N-N
>   NLNN
>   NNNN
>   Would you then just extend the case statements? I'm just completely
> confused about these types of statements, no matter how often I try to
> understand them.


Given that you cannot alter the database structure, I'd recommend that you
look at *greatly* simplify the query by creating a UDF and sorting on it's
results.  Example:

select columns...,
from mytable
order by myudf(numericsortvalue)

Then you can put all this obfuscated logic within the udf.  Just make sure
that the udf returns a numeric value as appropriate to get the records back
in order.

The key is to create a set of english language rules that govern how you
determine which records (based on pattern) are in which order.  Then take
your english language rules and create code to support them.

FYI, if this is truly black-box testing then you shouldn't be writing these
queries, but rather calling some sp written by somebody else.  Maybe you
could use that argument to gain a little leeway with the powers that be to
let you add the sorting columns.  Just my 2cents.

Good luck,

Joshua Olson
Web Application Engineer
WAE Tech Inc.

More information about the thelist mailing list