Mixing
Mysql Sort price , when price thousand to K, million to M
In a MySQL database, prices are stored in a way like this:
98.06K
97.44K
929.14K
91.87K
2.66M
146.64K
14.29K
when i try to sort price ASC or Price DESC, it returns unexpected result.
Kindly suggest me how can i sort price when price is in
10K, 20M, 1.6B
I want result
14.29K
91.87K
97.44K
98.06K
146.64K
929.14K
2.66M
mysql sql phpmyadmin
asked Nov 20 '18 at 10:56
asad appasad app
38118
add a comment |
In a MySQL database, prices are stored in a way like this:
98.06K
97.44K
929.14K
91.87K
2.66M
146.64K
14.29K
when i try to sort price ASC or Price DESC, it returns unexpected result.
Kindly suggest me how can i sort price when price is in
10K, 20M, 1.6B
I want result
14.29K
91.87K
97.44K
98.06K
146.64K
929.14K
2.66M
mysql sql phpmyadmin
asked Nov 20 '18 at 10:56
asad appasad app
38118
3
It's kinda weird to store prices like that, how is your database supposed to know what "K" and "M" is?
– maio290
Nov 20 '18 at 10:58
2
You store a price in a VarChar using this format? Don't do it.
– dnoeth
Nov 20 '18 at 10:59
I get prices from third party API, so i store it in VarChar, now i want to sort price.
– asad app
Nov 20 '18 at 11:01
3
Tell the third party to fix their code - numerical data should be stored numerically, and formatted only on output
– Clive
Nov 20 '18 at 11:02
2
And if you can't make them give you sensible data, you should convert the API data yourself before you store it, and place it in an appropriate numeric column. Then sorting is easy.
– ADyson
Nov 20 '18 at 11:13
add a comment |
In a MySQL database, prices are stored in a way like this:
98.06K
97.44K
929.14K
91.87K
2.66M
146.64K
14.29K
when i try to sort price ASC or Price DESC, it returns unexpected result.
Kindly suggest me how can i sort price when price is in
10K, 20M, 1.6B
I want result
14.29K
91.87K
97.44K
98.06K
146.64K
929.14K
2.66M
mysql sql phpmyadmin
asked Nov 20 '18 at 10:56
asad appasad app
38118
In a MySQL database, prices are stored in a way like this:
98.06K
97.44K
929.14K
91.87K
2.66M
146.64K
14.29K
when i try to sort price ASC or Price DESC, it returns unexpected result.
Kindly suggest me how can i sort price when price is in
10K, 20M, 1.6B
I want result
14.29K
91.87K
97.44K
98.06K
146.64K
929.14K
2.66M
mysql sql phpmyadmin
mysql sql phpmyadmin
asked Nov 20 '18 at 10:56
asad appasad app
38118
asked Nov 20 '18 at 10:56
asad appasad app
38118
asked Nov 20 '18 at 10:56
asad appasad app
38118
asked Nov 20 '18 at 10:56
asad appasad app
38118
asked Nov 20 '18 at 10:56
asad appasad app
38118
38118
3
It's kinda weird to store prices like that, how is your database supposed to know what "K" and "M" is?
– maio290
Nov 20 '18 at 10:58
2
You store a price in a VarChar using this format? Don't do it.
– dnoeth
Nov 20 '18 at 10:59
I get prices from third party API, so i store it in VarChar, now i want to sort price.
– asad app
Nov 20 '18 at 11:01
3
Tell the third party to fix their code - numerical data should be stored numerically, and formatted only on output
– Clive
Nov 20 '18 at 11:02
2
And if you can't make them give you sensible data, you should convert the API data yourself before you store it, and place it in an appropriate numeric column. Then sorting is easy.
– ADyson
Nov 20 '18 at 11:13
add a comment |
3
It's kinda weird to store prices like that, how is your database supposed to know what "K" and "M" is?
– maio290
Nov 20 '18 at 10:58
2
You store a price in a VarChar using this format? Don't do it.
– dnoeth
Nov 20 '18 at 10:59
I get prices from third party API, so i store it in VarChar, now i want to sort price.
– asad app
Nov 20 '18 at 11:01
3
Tell the third party to fix their code - numerical data should be stored numerically, and formatted only on output
– Clive
Nov 20 '18 at 11:02
2
And if you can't make them give you sensible data, you should convert the API data yourself before you store it, and place it in an appropriate numeric column. Then sorting is easy.
– ADyson
Nov 20 '18 at 11:13
3
3
It's kinda weird to store prices like that, how is your database supposed to know what "K" and "M" is?
– maio290
Nov 20 '18 at 10:58
It's kinda weird to store prices like that, how is your database supposed to know what "K" and "M" is?
– maio290
Nov 20 '18 at 10:58
2
2
You store a price in a VarChar using this format? Don't do it.
– dnoeth
Nov 20 '18 at 10:59
You store a price in a VarChar using this format? Don't do it.
– dnoeth
Nov 20 '18 at 10:59
I get prices from third party API, so i store it in VarChar, now i want to sort price.
– asad app
Nov 20 '18 at 11:01
I get prices from third party API, so i store it in VarChar, now i want to sort price.
– asad app
Nov 20 '18 at 11:01
3
3
Tell the third party to fix their code - numerical data should be stored numerically, and formatted only on output
– Clive
Nov 20 '18 at 11:02
Tell the third party to fix their code - numerical data should be stored numerically, and formatted only on output
– Clive
Nov 20 '18 at 11:02
2
2
And if you can't make them give you sensible data, you should convert the API data yourself before you store it, and place it in an appropriate numeric column. Then sorting is easy.
– ADyson
Nov 20 '18 at 11:13
And if you can't make them give you sensible data, you should convert the API data yourself before you store it, and place it in an appropriate numeric column. Then sorting is easy.
– ADyson
Nov 20 '18 at 11:13
add a comment |
3 Answers
3
active
oldest
votes
MySQL ignores trailing non-digits when casting string to numeric. This will return the correct price:
price *
case right(price,1)
when 'K' then 1000
when 'M' then 1000000
else 1
end
Of course, you can order by this, but you better apply it during load and store the price in a numeric column.
answered Nov 20 '18 at 11:07
dnoethdnoeth
45.2k31839
1
Don't you need to remove the K/M from price before multiplying?
– jarlh
Nov 20 '18 at 11:16
1
@jarlh: MySQL simply scans the input and stops when there's a non-digit (or a 2nd.) without failing, strange, isn't it?
– dnoeth
Nov 20 '18 at 11:26
Indeed it is. Thanks for the explanation.
– jarlh
Nov 20 '18 at 11:28
Thanks, can you please suggest me full query? how can i use it, i try my best to apply this, but its return error "unexpected near right"
– asad app
Nov 20 '18 at 11:40
@asadapp:order by price....
– dnoeth
Nov 20 '18 at 12:01
|
show 2 more comments
The problem lies in your data model. I understand that 2.66M is not necessarily exactly 2,660,000, which is why you don't want to store the whole number, but store '2.66M' instead to indicate the precision. This, however, is two pieces of information: the value and the precision, so use two columns:
mytable
value | unit
-------+-----
98.06 | K
97.44 | K
929.14 | K
91.87 | K
2.66 | M
146.64 | K
14.29 | K
Along with a lookup table:
units
unit | factor
-----+--------
K | 1000
M | 1000000
A possible query would be:
select *
from mytable
join units using (unit)
order by mytable.value * units.factor;
where you may want to extend the ORDER BY clause to something like
order by mytable.value * units.factor, units.factor;
or apply some rounding or whatever to consider precision of two seemingly equal values.
answered Nov 20 '18 at 11:14
Thorsten KettnerThorsten Kettner
51.1k22642
add a comment |
It is possible, though not advisable:
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=0a837287c7646823fa6657706f9ae634
SELECT *
, CAST(LEFT(price, LENGTH(price) - 1) AS DECIMAL(10,2)) AS value
, RIGHT(price, 1) AS unit
, CASE RIGHT(price,1)
WHEN 'K' THEN 1000
WHEN 'M' THEN 1000000
ELSE 1
END AS amount
FROM test1
ORDER BY amount, value;
Why not advisable? As the Explain in the dbfiddle shows, this query uses filesort for sorting, which is not very fast. If you do not have too many rows in your data, this should be no problem though.
answered Nov 20 '18 at 11:36
HerrSerkerHerrSerker
20.2k84779
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53391485%2fmysql-sort-price-when-price-thousand-to-k-million-to-m%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
MySQL ignores trailing non-digits when casting string to numeric. This will return the correct price:
price *
case right(price,1)
when 'K' then 1000
when 'M' then 1000000
else 1
end
Of course, you can order by this, but you better apply it during load and store the price in a numeric column.
answered Nov 20 '18 at 11:07
dnoethdnoeth
45.2k31839
1
Don't you need to remove the K/M from price before multiplying?
– jarlh
Nov 20 '18 at 11:16
1
@jarlh: MySQL simply scans the input and stops when there's a non-digit (or a 2nd.) without failing, strange, isn't it?
– dnoeth
Nov 20 '18 at 11:26
Indeed it is. Thanks for the explanation.
– jarlh
Nov 20 '18 at 11:28
Thanks, can you please suggest me full query? how can i use it, i try my best to apply this, but its return error "unexpected near right"
– asad app
Nov 20 '18 at 11:40
@asadapp:order by price....
– dnoeth
Nov 20 '18 at 12:01
|
show 2 more comments
MySQL ignores trailing non-digits when casting string to numeric. This will return the correct price:
price *
case right(price,1)
when 'K' then 1000
when 'M' then 1000000
else 1
end
Of course, you can order by this, but you better apply it during load and store the price in a numeric column.
answered Nov 20 '18 at 11:07
dnoethdnoeth
45.2k31839
1
Don't you need to remove the K/M from price before multiplying?
– jarlh
Nov 20 '18 at 11:16
1
@jarlh: MySQL simply scans the input and stops when there's a non-digit (or a 2nd.) without failing, strange, isn't it?
– dnoeth
Nov 20 '18 at 11:26
Indeed it is. Thanks for the explanation.
– jarlh
Nov 20 '18 at 11:28
Thanks, can you please suggest me full query? how can i use it, i try my best to apply this, but its return error "unexpected near right"
– asad app
Nov 20 '18 at 11:40
@asadapp:order by price....
– dnoeth
Nov 20 '18 at 12:01
|
show 2 more comments
MySQL ignores trailing non-digits when casting string to numeric. This will return the correct price:
price *
case right(price,1)
when 'K' then 1000
when 'M' then 1000000
else 1
end
Of course, you can order by this, but you better apply it during load and store the price in a numeric column.
answered Nov 20 '18 at 11:07
dnoethdnoeth
45.2k31839
MySQL ignores trailing non-digits when casting string to numeric. This will return the correct price:
price *
case right(price,1)
when 'K' then 1000
when 'M' then 1000000
else 1
end
Of course, you can order by this, but you better apply it during load and store the price in a numeric column.
answered Nov 20 '18 at 11:07
dnoethdnoeth
45.2k31839
answered Nov 20 '18 at 11:07
dnoethdnoeth
45.2k31839
answered Nov 20 '18 at 11:07
dnoethdnoeth
45.2k31839
answered Nov 20 '18 at 11:07
dnoethdnoeth
45.2k31839
45.2k31839
1
Don't you need to remove the K/M from price before multiplying?
– jarlh
Nov 20 '18 at 11:16
1
@jarlh: MySQL simply scans the input and stops when there's a non-digit (or a 2nd.) without failing, strange, isn't it?
– dnoeth
Nov 20 '18 at 11:26
Indeed it is. Thanks for the explanation.
– jarlh
Nov 20 '18 at 11:28
Thanks, can you please suggest me full query? how can i use it, i try my best to apply this, but its return error "unexpected near right"
– asad app
Nov 20 '18 at 11:40
@asadapp:order by price....
– dnoeth
Nov 20 '18 at 12:01
|
show 2 more comments
1
Don't you need to remove the K/M from price before multiplying?
– jarlh
Nov 20 '18 at 11:16
1
@jarlh: MySQL simply scans the input and stops when there's a non-digit (or a 2nd.) without failing, strange, isn't it?
– dnoeth
Nov 20 '18 at 11:26
Indeed it is. Thanks for the explanation.
– jarlh
Nov 20 '18 at 11:28
Thanks, can you please suggest me full query? how can i use it, i try my best to apply this, but its return error "unexpected near right"
– asad app
Nov 20 '18 at 11:40
@asadapp:order by price....
– dnoeth
Nov 20 '18 at 12:01
1
1
Don't you need to remove the K/M from price before multiplying?
– jarlh
Nov 20 '18 at 11:16
Don't you need to remove the K/M from price before multiplying?
– jarlh
Nov 20 '18 at 11:16
1
1
@jarlh: MySQL simply scans the input and stops when there's a non-digit (or a 2nd
.) without failing, strange, isn't it?– dnoeth
Nov 20 '18 at 11:26
@jarlh: MySQL simply scans the input and stops when there's a non-digit (or a 2nd
.) without failing, strange, isn't it?– dnoeth
Nov 20 '18 at 11:26
Indeed it is. Thanks for the explanation.
– jarlh
Nov 20 '18 at 11:28
Indeed it is. Thanks for the explanation.
– jarlh
Nov 20 '18 at 11:28
Thanks, can you please suggest me full query? how can i use it, i try my best to apply this, but its return error "unexpected near right"
– asad app
Nov 20 '18 at 11:40
Thanks, can you please suggest me full query? how can i use it, i try my best to apply this, but its return error "unexpected near right"
– asad app
Nov 20 '18 at 11:40
@asadapp:
order by price....– dnoeth
Nov 20 '18 at 12:01
@asadapp:
order by price....– dnoeth
Nov 20 '18 at 12:01
|
show 2 more comments
The problem lies in your data model. I understand that 2.66M is not necessarily exactly 2,660,000, which is why you don't want to store the whole number, but store '2.66M' instead to indicate the precision. This, however, is two pieces of information: the value and the precision, so use two columns:
mytable
value | unit
-------+-----
98.06 | K
97.44 | K
929.14 | K
91.87 | K
2.66 | M
146.64 | K
14.29 | K
Along with a lookup table:
units
unit | factor
-----+--------
K | 1000
M | 1000000
A possible query would be:
select *
from mytable
join units using (unit)
order by mytable.value * units.factor;
where you may want to extend the ORDER BY clause to something like
order by mytable.value * units.factor, units.factor;
or apply some rounding or whatever to consider precision of two seemingly equal values.
answered Nov 20 '18 at 11:14
Thorsten KettnerThorsten Kettner
51.1k22642
add a comment |
The problem lies in your data model. I understand that 2.66M is not necessarily exactly 2,660,000, which is why you don't want to store the whole number, but store '2.66M' instead to indicate the precision. This, however, is two pieces of information: the value and the precision, so use two columns:
mytable
value | unit
-------+-----
98.06 | K
97.44 | K
929.14 | K
91.87 | K
2.66 | M
146.64 | K
14.29 | K
Along with a lookup table:
units
unit | factor
-----+--------
K | 1000
M | 1000000
A possible query would be:
select *
from mytable
join units using (unit)
order by mytable.value * units.factor;
where you may want to extend the ORDER BY clause to something like
order by mytable.value * units.factor, units.factor;
or apply some rounding or whatever to consider precision of two seemingly equal values.
answered Nov 20 '18 at 11:14
Thorsten KettnerThorsten Kettner
51.1k22642
add a comment |
The problem lies in your data model. I understand that 2.66M is not necessarily exactly 2,660,000, which is why you don't want to store the whole number, but store '2.66M' instead to indicate the precision. This, however, is two pieces of information: the value and the precision, so use two columns:
mytable
value | unit
-------+-----
98.06 | K
97.44 | K
929.14 | K
91.87 | K
2.66 | M
146.64 | K
14.29 | K
Along with a lookup table:
units
unit | factor
-----+--------
K | 1000
M | 1000000
A possible query would be:
select *
from mytable
join units using (unit)
order by mytable.value * units.factor;
where you may want to extend the ORDER BY clause to something like
order by mytable.value * units.factor, units.factor;
or apply some rounding or whatever to consider precision of two seemingly equal values.
answered Nov 20 '18 at 11:14
Thorsten KettnerThorsten Kettner
51.1k22642
The problem lies in your data model. I understand that 2.66M is not necessarily exactly 2,660,000, which is why you don't want to store the whole number, but store '2.66M' instead to indicate the precision. This, however, is two pieces of information: the value and the precision, so use two columns:
mytable
value | unit
-------+-----
98.06 | K
97.44 | K
929.14 | K
91.87 | K
2.66 | M
146.64 | K
14.29 | K
Along with a lookup table:
units
unit | factor
-----+--------
K | 1000
M | 1000000
A possible query would be:
select *
from mytable
join units using (unit)
order by mytable.value * units.factor;
where you may want to extend the ORDER BY clause to something like
order by mytable.value * units.factor, units.factor;
or apply some rounding or whatever to consider precision of two seemingly equal values.
answered Nov 20 '18 at 11:14
Thorsten KettnerThorsten Kettner
51.1k22642
answered Nov 20 '18 at 11:14
Thorsten KettnerThorsten Kettner
51.1k22642
answered Nov 20 '18 at 11:14
Thorsten KettnerThorsten Kettner
51.1k22642
answered Nov 20 '18 at 11:14
Thorsten KettnerThorsten Kettner
51.1k22642
51.1k22642
add a comment |
add a comment |
It is possible, though not advisable:
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=0a837287c7646823fa6657706f9ae634
SELECT *
, CAST(LEFT(price, LENGTH(price) - 1) AS DECIMAL(10,2)) AS value
, RIGHT(price, 1) AS unit
, CASE RIGHT(price,1)
WHEN 'K' THEN 1000
WHEN 'M' THEN 1000000
ELSE 1
END AS amount
FROM test1
ORDER BY amount, value;
Why not advisable? As the Explain in the dbfiddle shows, this query uses filesort for sorting, which is not very fast. If you do not have too many rows in your data, this should be no problem though.
answered Nov 20 '18 at 11:36
HerrSerkerHerrSerker
20.2k84779
add a comment |
It is possible, though not advisable:
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=0a837287c7646823fa6657706f9ae634
SELECT *
, CAST(LEFT(price, LENGTH(price) - 1) AS DECIMAL(10,2)) AS value
, RIGHT(price, 1) AS unit
, CASE RIGHT(price,1)
WHEN 'K' THEN 1000
WHEN 'M' THEN 1000000
ELSE 1
END AS amount
FROM test1
ORDER BY amount, value;
Why not advisable? As the Explain in the dbfiddle shows, this query uses filesort for sorting, which is not very fast. If you do not have too many rows in your data, this should be no problem though.
answered Nov 20 '18 at 11:36
HerrSerkerHerrSerker
20.2k84779
add a comment |
It is possible, though not advisable:
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=0a837287c7646823fa6657706f9ae634
SELECT *
, CAST(LEFT(price, LENGTH(price) - 1) AS DECIMAL(10,2)) AS value
, RIGHT(price, 1) AS unit
, CASE RIGHT(price,1)
WHEN 'K' THEN 1000
WHEN 'M' THEN 1000000
ELSE 1
END AS amount
FROM test1
ORDER BY amount, value;
Why not advisable? As the Explain in the dbfiddle shows, this query uses filesort for sorting, which is not very fast. If you do not have too many rows in your data, this should be no problem though.
answered Nov 20 '18 at 11:36
HerrSerkerHerrSerker
20.2k84779
It is possible, though not advisable:
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=0a837287c7646823fa6657706f9ae634
SELECT *
, CAST(LEFT(price, LENGTH(price) - 1) AS DECIMAL(10,2)) AS value
, RIGHT(price, 1) AS unit
, CASE RIGHT(price,1)
WHEN 'K' THEN 1000
WHEN 'M' THEN 1000000
ELSE 1
END AS amount
FROM test1
ORDER BY amount, value;
Why not advisable? As the Explain in the dbfiddle shows, this query uses filesort for sorting, which is not very fast. If you do not have too many rows in your data, this should be no problem though.
answered Nov 20 '18 at 11:36
HerrSerkerHerrSerker
20.2k84779
edited Nov 20 '18 at 11:59
answered Nov 20 '18 at 11:36
HerrSerkerHerrSerker
20.2k84779
answered Nov 20 '18 at 11:36
HerrSerkerHerrSerker
20.2k84779
answered Nov 20 '18 at 11:36
HerrSerkerHerrSerker
20.2k84779
20.2k84779
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53391485%2fmysql-sort-price-when-price-thousand-to-k-million-to-m%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
It's kinda weird to store prices like that, how is your database supposed to know what "K" and "M" is?
– maio290
Nov 20 '18 at 10:58
2
You store a price in a VarChar using this format? Don't do it.
– dnoeth
Nov 20 '18 at 10:59
I get prices from third party API, so i store it in VarChar, now i want to sort price.
– asad app
Nov 20 '18 at 11:01
3
Tell the third party to fix their code - numerical data should be stored numerically, and formatted only on output
– Clive
Nov 20 '18 at 11:02
2
And if you can't make them give you sensible data, you should convert the API data yourself before you store it, and place it in an appropriate numeric column. Then sorting is easy.
– ADyson
Nov 20 '18 at 11:13