YumYumYum YumYumYum - 2 months ago 6
MySQL Question

Output isn't ordered properly

How to order the result by C, D, A, B and

pincode
chronologically?

Original result :

S Pincode
== =======
A 001
B 002
C 003
D 004
D 005
C 006
B 007
A 008


Expected result:

S Pincode
== =======
C 003
C 006

D 004
D 005

A 001
A 008

B 002
B 007


Code:

SELECT
id,
sector,
pincode

FROM
sh_av_spform

WHERE

type='ticket' and
status='new' and
date(`createdate`) = CURDATE()

ORDER BY

FIELD( sector, 'C','D','A','B' ) ASC

limit 5


Above SQL, Gives sometimes, not correctly ordered pincode in chronological
sector


Invalid output i get such as:

S Pincode
== =======
C 003
C 006

D 005
D 004 <<< ???

A 001
A 008

B 007
B 002 <<< ???


Anyone know how to fix this?

Answer

You only order by one column. Add the second one too:

SELECT 
  id,
  sector,
  pincode 

FROM 
  sh_av_spform 

WHERE 

  type='ticket' and 
  status='new' and 
  date(`createdate`) = CURDATE()

ORDER BY  

  FIELD( sector,  'C','D','A','B' ) ASC,  
  pincode ASC
Comments