การเรียง sql order ตัวเลขแบบมนุษย์

ปกติแล้วเวลาที่เราเขียนคำสั่ง sql ให้เรียงตามมากไปน้อย หรือ น้อยไปมาก เช่น

SELECT * FROM `products` ORDER BY `id` ASC

หากช่องเก็บ id เป็น varchar/text ผลลัพธ์จะออกมาเป็น 1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 23

มันไม่เรียงตามจำนวนจากมากไปน้อย เนื่องจาก computer มันเรียงของมันอย่างนั้น (หากย้อนไป Windows ยุคเก่าๆมันก็เรียงอย่างนี้เหมือนกัน)

ซึ่งทั้งๆที่เราระบุไว้แล้วว่าเรียงจากน้อยไปมาก (asc)

วิธีแก้

วิธีที่ 1

เพิ่ม ABS() เข้าไปใน field ที่จะสั่งให้เรียง เช่น

SELECT * FROM `products` ORDER BY ABS(`id`) ASC

ซึ่ง ABS() นี้ก็คือ absolute value หรือค่าจริงนี่เอง (อ้างอิงจาก https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html). วิธีนี้มีข้อจำกัดคือ ค่าของตัวเลขในตารางต้องไม่มีค่าผสมกันระหว่าง + กับ - เช่น -1, 0, 1, 2 แบบนี้จะเรียงไม่ถูก.

วิธีที่ 2

ใช้ +0 เข้าไป เช่น

SELECT * FROM `products` ORDER BY `id`+0 ASC

วิธีที่ 3

ใช้ CAST()

SELECT * FROM `products` ORDER BY CAST(`id` AS SIGNED) ASC

CAST() คือแปลงค่าให้เป็นประเภทที่ระบุ (อ้างอิงจาก https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast).

คราวนี้ผลลัพธ์ที่จะออกมาก็ควรจะเป็น 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 ตามการเรียงเลขแบบที่มนุษย์เข้าใจ

เทคนิคนี้สามารถใช้ได้ทั้ง microsoft sql และ mysql