Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 941
  • Last Modified:

Is there a way to sort a query on odd/even numbers

I have a database of buildings.  I would like to sort it by odd/even address number.  Give me all the odds first followed by the evens.
0
breeze351
Asked:
breeze351
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
yes:

order by
case when cast (address_number as int) % 2 = 1 then SELECT 0; 
else 
SELECT 1 ;
end case

Open in new window



More on the case statement here:
http://dev.mysql.com/doc/refman/5.0/en/case.html

If you don't have address_number as its only field you can do a substring on the address column and use a char index to find the space between the number and the Street name.
0
 
quizwedgeCommented:
Not sure, but this might be faster than a case statement. It's cleaner, at least.

select * 
from buildings 
order by address_number %2 = 0

Open in new window


If you want the addresses in numerical order,

select * 
from buildings 
order by address_number %2 = 0, address_number

Open in new window

0
 
breeze351Author Commented:
Thanks that works
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now