SQL Server - how to get maximum of decimal number from my table??

Asked By mani on 09-Mar-12 05:44 AM
hi i have number like

0001.1
0001.2
0001.3
0001.4
.
.
.
0001.9
0001.10
0001.11

like this
also

0002.1
0002.2
..
how can i get maximum number and what datatype have to create in my table???any help??
Reena Jain replied to mani on 09-Mar-12 05:52 AM
Hi

You can use max function to date decimal highest value from database table like this

select max(num) from table1


or if you want to fetch two highest number then use below query

select top 2 num from table1 order by num desc

Hope this will help you

mani replied to Reena Jain on 09-Mar-12 06:30 AM
hi...thanks for u r reply..but if get maximum value ...
i cant get after 1.9,,
if i have
 1.10,
1.11
than also i am getting 1.9 only..so after 1.9  i cant get max value .like(1.10)(1.11) like this..what will be the solution.???
Sandeep Mittal replied to mani on 09-Mar-12 08:27 AM
Dear Mani

Mathematically 1.9 is greater than 1.10, so you are getting the result as 1.9.
your data should be like this - 1.01, 1.02, 1.03,..........1.09, 1.10
Once you have the data like this you would get proper max value
Pat Hartman replied to mani on 10-Mar-12 12:28 AM
What you have currently is a string, not a number.  Strings are sorted character by character, left to right.  To solve your problem, store the value in two integer fields.  Then concatenate the two fields with leading zeros for each part if that is how you want them displayed.  You could store the value as a single currency field but you would need to know now whether you would ever need more than two digits to the right of the decimal.  Otherwise, you end up with 0001.100 sorting after 0001.10 rather than after 0001.99 which would have to be stored as 0001.099 to sort correctly.  Personally, I would store this value as two integers.  Keeping the two parts separate will allow you to actually store them as integers so they will always sort correctly.  Then when you concatenate them for printing, you won't even need leading zeros unless that is actually how you want the field formatted.