order by case using SQL

Question

I have the following table:

+----+----------+-----+-----------+----------+
| 1 | Ramesh   | 32 | Ahmedabad | 2000.00 |
| 7 | Muffy    | 24 | Indore    | 10000.00 |
| 6 | Komal    | 22 | MP       | 4500.00 |
| 2 | Khilan   | 25 | Delhi     | 1500.00 |
| 3 | kaushik  | 23 | Kota      | 2000.00 |
| 5 | Hardik   | 27 | Bhopal    | 8500.00 |
| 4 | Chaitali | 25 | Mumbai    | 6500.00 |
+----+----------+-----+-----------+----------+

And, I am using the following query to sort the table as per my preferred order, but I am getting the following SQL error:

CURSOR OPERATION CONFLICT

My query:

SELECT * FROM CUSTOMERS ORDER BY (CASE ADDRESS WHEN 'DELHI'   THEN 1
WHEN 'BHOPAL'   THEN 2
WHEN 'KOTA'   THEN 3
WHEN 'AHMADABAD' THEN 4
WHEN 'MP'  THEN 5
ELSE 100 END) ASC, ADDRESS DESC

Answer 1

Since Access does not suport CASE...WHEN, you have two options to get a custom sort order.

1) If you can edit the database structure, add a "SortOrder" column to the table.

Assuming the "Address" field has unique values in the "CUSTOMERS" table, create a field in the "CUSTOMERS" table called "SortOrder", specifying "1" for "Delhi", "2" for "Bhopal", and so on:

SELECT *
FROM CUSTOMERS
ORDER BY CUSTOMERS.SortOrder DESC;

However, if it"s possible for the same "Address" value to appear multiple times in the "CUSTOMERS" table, you should create a new table like "ADDRESSES" that contains the Address and SortOrder values. The SQL would ultimately look like this:

SELECT CUSTOMERS.* 
FROM CUSTOMERS INNER JOIN ADDRESSES ON CUSTOMERS.Address = ADDRESSES.Address 
ORDER BY ADDRESSES.SortOrder DESC;

2) Alternatively, if you can"t edit the database structure, using the IIf() function in the ORDER BY clause:

SELECT *
FROM CUSTOMERS
ORDER BY IIf([ADDRESS] = 'DELHI',1,IIf([ADDRESS] = 'BHOPAL',2,
IIf([ADDRESS] = 'KOTA',3,IIf([ADDRESS] = 'AHMADABAD',4,
IIf([ADDRESS] = 'MP',5,6))))) DESC;

You should avoid doing this unless you can't edit the database structure because function calls are slow, especially when multiple conditions are evaluated as in this example. If you need to specify any additional sorting for the "ADDRESS" field, you will need to add additional IIf() functions, which will slow this down even further.

If you're able to specify a sort order for every value of "ADDRESS", you should use a single Switch() function instead of nested IIf() functions.