CODEWITHSUNDEEP
mysqlsql
Micheal
Micheal

Reputation: 2322

Sort by a particular value

I have this query which might return multiple results. Is it possible to sort it and display in the following order: pending, failed, application, submitted, canceled ?

i.e sort by pa.status as above.

SELECT
  cl.id,cl.lead_id,cl.client_name,po.id,po.carrier,
  pa.downpayment_time,pa.status,pa.policy_id
FROM
  pdp_client_info AS cl,
  pdp_policy_info AS po,
  pdp_payment AS pa
WHERE
  cl.id = po.id AND po.id=pa.policy_id
 AND pa.downpayment_date = '$current_date'
 AND (pa.status='pending'
   OR pa.status='failed'
   OR pa.status='application'
   OR pa.status='submitted'
   OR pa.status='canceled')

Upvotes: 1

Views: 85

Answers (3)

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57650

I'd suggest you to change the column type to Enum. This will be good for future.

ALTER TABLE pdp_client_info  ADD t_status ENUM ('PENDING','FAILED','APPLICATION','SUBMITTED','CANCELED'); 
-- t_status is temporary status
UPDATE pdp_client_info SET `t_status` = UPPER(`status`);
ALTER TABLE pdp_client_info  MODIFY `status` ENUM ('PENDING','FAILED','APPLICATION','SUBMITTED','CANCELED');
UPDATE pdp_client_info SET `status` = `t_status`;

Now just add ORDER BY pa.status. It'll work all the time.

Upvotes: 1

J. Bruni
J. Bruni

Reputation: 20492

Use the FIND_IN_SET function:

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set

Code will look like this:

ORDER BY FIND_IN_SET(pa.status, 'pending,failed,application,submitted,canceled')

Here is how I would rewrite your SQL query:

SELECT
  cl.id, cl.lead_id, cl.client_name, 
  po.id, po.carrier,
  pa.downpayment_time, pa.status, pa.policy_id
FROM
  pdp_client_info AS cl
  JOIN pdp_policy_info AS po ON (cl.id = po.id)
  JOIN pdp_payment AS pa ON (po.id = pa.policy_id)
WHERE
  (pa.downpayment_date = '$current_date')
  AND (pa.status IN ('pending', 'failed', 'application', 'submitted', 'canceled'))
ORDER BY
  FIND_IN_SET(pa.status, 'pending,failed,application,submitted,canceled')

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

...
ORDER BY CASE WHEN pa.status = 'pending' THEN 1
              WHEN pa.status = 'failed' THEN 2
              WHEN pa.status = 'application' THEN 3
              WHEN pa.status = 'submitted' THEN 4
              WHEN pa.status = 'canceled' THEN 5
              ELSE 6
         END

Upvotes: 1

Related Questions