|Posted: 10/22/2008, 2:46 PM
I needed to create a complex sort order for a listbox, which went beyond the normal "ORDER BY" and "GROUP BY" parameters in an SQL query.
I needed to apply some conditions to define my sort order. Have a look at the code - it may be helpful.
You basically apply each of your conditions after CASE WHEN ...
SELECT ENTID, concat(CompanyName,": ",EntityTypeRef) 2cols,
WHEN EntityTypeRef = 'Customer' or EntityTypeRef = 'Job' THEN 1
WHEN EntityTypeRef = 'Supplier' THEN 2
WHEN EntityTypeRef = 'Employee' THEN 3
WHEN EntityTypeRef = 'Prospect' THEN 4
WHERE EntityTypeRef = 'Customer'
or EntityTypeRef = 'Job'
or EntityTypeRef = 'Supplier'
or EntityTypeRef = 'Employee'
or EntityTypeRef = 'Prospect'
In this example I needed to order by Customer and Job (this first category then ordered by CompanyName then EntityType) then Supplier then Employee then Prospect (not alphabetical !)
With a little imagination, you can do some very specific ordering!
RAD tools for rich UI controls: