andy
Posts: 183
|
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,
CASE
WHEN EntityTypeRef = 'Customer' or EntityTypeRef = 'Job' THEN 1
WHEN EntityTypeRef = 'Supplier' THEN 2
WHEN EntityTypeRef = 'Employee' THEN 3
WHEN EntityTypeRef = 'Prospect' THEN 4
ELSE 5
END orderSequence
FROM entity
WHERE EntityTypeRef = 'Customer'
or EntityTypeRef = 'Job'
or EntityTypeRef = 'Supplier'
or EntityTypeRef = 'Employee'
or EntityTypeRef = 'Prospect'
ORDER BY
orderSequence,
entity.CompanyName ASC,
entity.EntityTypeRef ASC
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!
_________________
Andy
RAD tools for rich UI controls:
http://www.koolphptools.com |