0

Possible Duplicate:
Ordering by the order of values in a SQL IN() clause

I have a simple query statement that pulls properties in a database by zip codes. I use an IN() statement to pass in multiple zip codes. If those zip codes are in order (increasingly further from a target zip code) is it possible to order the results by the order the zip codes are passed into the IN() statement?

For example say i have the zip codes: 77808, 77805, 77806, 77807. I pass them into my query like so

$query = "SELECT * FROM Properties WHERE zip_code IN('77808', '77805', '77806', '77807',)";

Could i order the results by properties that have the zip code of 77808 first, then 77805, then 77806, etc.? or would i need to order it after i pulled it out of the database with PHP(in this case)?

Community
  • 1
  • 1
David
  • 10,418
  • 17
  • 72
  • 122
  • 3
    Mysql? See http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause – Ryan Jun 07 '12 at 04:07

2 Answers2

2

If you use Mysql, then you could use the FIELD function.

SELECT * FROM Properties 
WHERE zip_code IN('77808', '77805', '77806', '77807')
ORDER BY FIELD(zip_code, '77808', '77805', '77806', '77807')
Ryan
  • 26,884
  • 9
  • 56
  • 83
xdazz
  • 158,678
  • 38
  • 247
  • 274
0
SELECT * FROM (
   SELECT 1 as my_order, p.* FROM Properties p WHERE zip_code = '77808'
   UNION ALL
   SELECT 2 as my_order, p.* FROM Properties p WHERE zip_code = '77805'
   UNION ALL 
   SELECT 3 as my_order, p.* FROM Properties p WHERE zip_code = '77806'
   UNION ALL
   SELECT 4 as my_order, p.* FROM Properties p WHERE zip_code = '77807'
) temp_table
ORDER BY my_order ASC
Luc M
  • 16,630
  • 26
  • 74
  • 89
  • My solution is not very optimal. If you use postgresql, you might be interested by http://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql – Luc M Jun 07 '12 at 04:25