mamatha mamatha - 7 months ago 11
SQL Question

data ordering issue in mysql for a particular column

I have a table in which there is column name as mappedcloumnname and fieldname and my fieldcolumn contains

address1
,
address2
,
city
,
state
,
customerid
,
country
and
mappedcolumn
contanins
c1-c20
. I wrote a query to sort my data based on
mappedcolumn
name but the order what am getting is wrong one

SELECT * FROM customermetadata
WHERE OrgID = in_orgid
ORDER BY MappedColumnName;

Answer

You can give it a try:

SELECT
    *
FROM
    customermetadata
WHERE
    OrgID = in_orgid
ORDER BY CAST(SUBSTRING(MappedColumnName FROM 2) AS UNSIGNED);

Note:

Here I've extracted the number from the MappedColumnName and sort the records based the extracted numbers.

I've created a demo where the table contains only two columns (id and col).

col column contains value like c1,c2,....

See demo if you order by col only.

See demo if you order by extracting number from col.

Comments