advanced code snippet search

Posted By

aamirrajpoot on 10/01/10


Tagged

sorting


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

bobbym245


Alpha Numeric Sorting in MySQL

 / Published in: MySQL
 

Okay,

If it is required to sort columns like this

1A 1a 10A 9B 21C 1C 1D

And Our desired Result is

1A 1C 1D 1a 9B 10A 21C

We can use casting, but casting will not sort 1A, 1B,1C properly. To overcome this issue we are first converting this column to binary and sorting the binary combined with tbl_column.

hehe,,, after doing some headbanging just did it with casting too... and using same logic... damn why I didnt thought of that.

Expand | Embed | Plain Text 
  1. BIN Way
  2. ===================================
  3. SELECT 
  4. tbl_column, 
  5. BIN(tbl_column) AS binray_not_needed_column
  6. FROM db_table
  7. ORDER BY binray_not_needed_column ASC , tbl_column ASC
  8.  
  9. -----------------------
  10.  
  11. Cast Way
  12. ===================================
  13. SELECT 
  14. tbl_column, 
  15. CAST(tbl_column as SIGNED) AS casted_column
  16. FROM db_table
  17. ORDER BY casted_column ASC , tbl_column ASC

Report this snippet  

You need to login to post a comment.