How to concatenate two integers into a string without losing precision?

I have the following table:

id	BigNumber	SmallNumber
1	1	100
2	1	200
3	2	1
4	2	25
5	3	3
6	3	50

The big number and small number in each row will be combined to make one number. The big number will go to the left of the decimal point, and the small number go to the right of the decimal point. (e.g. Row 1 makes 1.100) I want to make a query that returns the largest number possible with these rules. I also don't want any kind of rounding (e.g. Row 1 should make 1.100 and not 1.1). With the data above, the query should return 3.50.

Here is the query I came up with

select max(cast(concat(big_number,'.',small_number) as decimal)) from runs;

The query returns 4. I know that I can do something like decimal(3,2), but the numbers will always be different depending on how many digits big number and small number have. How can I make the query always return the correct string representation of the number with the correct precision?

Solution

In MySQL, you can use LPAD to maintain 0 padding on small number. Here is the sample code:

SELECT CONCAT(big_number, '.', LPAD(small_number, LENGTH(small_number), '0')) AS concatenated_number
FROM runs
ORDER BY CAST(CONCAT(big_number, '.', LPAD(small_number, LENGTH(small_number), '0')) AS DECIMAL) DESC
LIMIT 1;

Here is the sample output :

concatenated_number
3.50

Here is the fiddle link

UPDATE:

In case you have data 9,5, 9,55 and 9.555 , you can check with this code instead code above

SELECT CONCAT(big_number, '.', LPAD(small_number, LENGTH(small_number), '0')) AS concatenated_number
FROM runs
WHERE CONCAT(big_number, '.', LPAD(small_number, LENGTH(small_number), '0')) = (
    SELECT MAX(CAST(CONCAT(big_number, '.', LPAD(small_number, LENGTH(small_number), '0')) AS FLOAT))
    FROM runs
);

This code should make more numerically stable even you reorder the input data.

Here is the fiddle link

The LPAD function is used to ensure that the small_number part of the concatenated value has additional zeros if needed. For example, when the small_number is 55, which doesn't require extra padding, LPAD doesn't modify it; it remains as 55. However, when the small_number is 50, LPAD adds an additional zero, resulting in .50 instead of .5. Its purpose is to maintain consistency in the format of the concatenated number.