Skip to content

Order a string column as an integer in Mysql

Use the following syntax to use a string/ varchar column as a number for ordering in MySQL.

SELECT * FROM <table> ORDER BY CAST(<column> AS unsigned)
Advertisements

Example

SELECT
		DATE,
		BOX,
		COUNT(DISTINCT ITEM) as ITEM_COUNT
	FROM
		first_sheet_output
	GROUP BY
		DATE,
		BOX
	ORDER BY
		DATE,
		CAST(BOX as UNSIGNED)

Here Box is of VARCHAR type. Hence I need to cast it as UNSIGNED to order it in ASCENDING order.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.