#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    56
    Rep Power
    3

    Finding Max Value in a Row


    I've encountered this problem a few times in my sad and short SQL career. I have a table with rows and I know there are 298 records in the field labeled PRIMARY KEY:

    Code:
    SELECT COUNT(*) AS total_usrs
    FROM users;
    This tells me there are '298' records but the values are not configured in a sequence so in order for me to enter a new row of data into the users table, I need to find out what the maximum value listed in the users_id field. If I look up the users_id for the row # 298 (not sure how to do that in ANSI SQL either ) but that could give me a value of 2000012 & there's no guarantee that the last row in the field is using the maximum value since ORDER BY isn't really functional when I use COUNT(*). Can someone please tell me the easiest way to locate the maximum value used on a particular column / field using a simple SQL statement? I search Google and some manual references and
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,174
    Rep Power
    4274
    Originally Posted by CarlosinFL
    ... the easiest way to locate the maximum value used on a particular column / field
    Code:
    SELECT *
      FROM daTable
    ORDER
        BY dis_column_here DESC LIMIT 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    56
    Rep Power
    3
    Code:
    forza=# SELECT cust_id AS highest_value
    FROM customers
    ORDER
        BY cust_id DESC LIMIT 1;
     highest_value
    ---------------
     10005
    (1 row)
    That worked great

    But is there a reason I would want to use that code over the following in regards to performance and or proper ANSI SQL?

    Code:
    forza=# SELECT
    forza-# max(cust_id)
    forza-# FROM customers
    forza-# ;
        max
    ------------
     10005
    (1 row)
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,174
    Rep Power
    4274
    my apologies, i thought i was in the mysql forum when i gave my answer

    did you want just the maximum value?

    or the row with the maximum value?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo