CodeCharge Studio
search Register Login  

Visual Web Reporting

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

YesSoftware Forums -> CodeCharge Studio -> Tips & Solutions

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

Print topic Send  topic

Author Message

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,  
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'  
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!

RAD tools for rich UI controls:
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

Internet Database

Visually create Web enabled database applications in minutes.

Home   |    Search   |    Members   |    Register   |    Login

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