You must Sign In to post a response.
  • Category: SQL Server

    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
  • #762813
    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

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

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

  • #762814
    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

    not the exact answer

  • #762818
    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.

  • #762821
    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

  • #762830
    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

  • #762833
    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

  • #762842

    Hi
    Your Query this


    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 Good

    SELECT 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

  • #763217
    select max(contactID) from tablename

  • #763302
    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


Sign In to post your comments