+ Reply to Thread
Results 1 to 2 of 2

Thread: retrieving max number from column

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    35

    retrieving max number from column

    I am running a sql query to return the largest value in a column back to the program.
    The sql query i run is.
    Code:
    select MAX([Line No]) from table
    The table has 3 rows, 'Line No' has values 50, 90, 100
    the max value i expect to be returned is 100, but im getting 90..why?
    Code:
      string LineNo = "0";
    
       //LineNo
       string SQLlineno = "select MAX(CAST([Line No] As Int)) from Saved_Order_Import where [order no] = '"
                          + ordernumber + "'";
       SqlCommand myCommandlineno = new SqlCommand(SQLlineno, myConnection);
       SqlDataReader readerline;
       try
       {
           readerline = myCommandlineno.ExecuteReader();
           if (readerline.HasRows)
           {
               while (readerline.Read())
               {
                   try
                   {
                       if (readerline.GetString(0) != null)
                       {
                           LineNo = (readerline.GetString(0) + 10).ToString();
                       }
                   }
                   catch (Exception ex) { return "{\"error\":\"line : " + ex.ToString() + "\"}"; }
               }
           }
           readerline.Close();
       }
       catch (Exception ex)
       {
          // return "{\"error\":\"Other One11" + ex.ToString() + "\"}";
       }

  2. #2
    Junior Member
    Join Date
    Jan 2012
    Posts
    19
    Your column is likely not a numeric type but a string type (char, nchar, varchar, nvarchar). So, it is sorting alphabetically, and 9 comes after 1.

    The easiest thing is to change to the correct data type for that column. If you can't do that, you'll need to cast back to the correct type inside the MAX.

    E.g.,
    Code:
    select max(cast ([Line No] as int)) 
    from table
    If you have some values in that column that cannot be converted to a number that you want to filter out, you can do:
    Code:
    select max(cast ([Line No] as int)) 
    from table 
    where isnumeric([Line No]) = 1
    Or, to only show the non-numeric values, so you can fix them, you could do:
    Code:
    select * from table where isnumeric([Line No]) = 0

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts