• # Getting max value of a column

hi
i am having a table table1 with columns and values as
id plot_no contract_id
2474 911
2518 1136 1171-15
2554 11365 628-14
2594 1548 2321-14
2595 584 1111-15

total 2000 rows i have like this

want to get the max of the column contract_id. from the above example the value should be 2321
• Hi Aparna,

Have you Google it the same, this is very basic thing. Use MAX function to achieve your task.

EX:
`select MAX(Contract_Id) from tablename`

--------------------------------------------------------------------------------

N@veen
Blog : http://naveens-dotnet.blogspot.in/

• hi
not getting by using that query.don't know exact reason. when i am using
SELECT MAX(contract_id) FROM table1
i am getting the answer as 999-14

• Hi Aparna.

First of all your column doesn't have the correct value to apply MAX function to it.

What is the datatype you used for the column Contract_id as its a value separated by hypen(-).

If the column is with only values like 628,2321,1136.. it definitely returns 2321 by using
select MAX(Contract_id) from TableName.

Sridhar Thota.
Editor: DNS Forum.

• Hai Aparna,
First of all, you need to get the Indexof '-' and then get the substring before the '-' and convert it to INT and then you can sort it to get the max id.
e.g.
`SELECT CAST (SUBSTRING('1171-15',1, CHARINDEX('-', '1171-15')-1) as int)`
This will give you 1171 as the integer.
Similarly you can use it in your select statement as:
`SELECT MAX(CAST (SUBSTRING(contract_id,1, CHARINDEX('-', contract_id)-1) as int)) From Table1`
Hope it will be helpful to you.

Regards,
Pawan Awasthi(DNS MVM)
+91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
pawansoftit@gmail.com

• Hello Aparna,

You can try this query :

SELECT MAX(CONVERT (INT,(CAST(SUBSTRING(contract_id,1,CHARINDEX('-', contract_id)-1) AS NUMERIC)))) FROM temptable

See the attached image. It's an output i have got.

Hope this will work for you.

Regards,
Nirav Lalan
DNS Gold Member
"Failure is the path of least persistence"

Delete Attachment

• thank u Mr.Nirav and Mr.Pawan. when i worked with the query, showing the error as "Invalid length parameter passed to the LEFT or SUBSTRING function.
". i think this could be because i don't have values in all the rows for all contract_id, some of the value is blank.
so pls help me to find a solution

• Hi

`SELECT MAX(CAST (SUBSTRING(isnull(contract_id,0),1, CHARINDEX('-', isnull(contract_id,0))-1) as int)) From Ttt1`

so you meet this issue

--Invalid length parameter passed to the LEFT or SUBSTRING function.

I correct and post this try this

`--Working GoodSELECT isnull(MAX(CAST (SUBSTRING(contract_id,1, CHARINDEX('-', isnull(contract_id,0))-1) as int)),0) From Ttt1`

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

Delete Attachment

• select max(contactID) from tablename

• Hello,

Little bit complicated to find out the maximum value from string field.For doing this we havev to substring the value and then convert the data type accordingly to get the maximum value.

Try with following query :-

select ISNULL(MAX(convert(int,Substring(contract_id,0,charindex('.',ISNULL(contract_id,0))))),0) from Table1

Above query helps you a lot.

Thanks