CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> Tips & Solutions

 Complex ORDER BY SQL queries (CASE WHEN...)

Print topic Send  topic

Author Message
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
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.

CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.