Finding Max Value in a Row

Dev Shed Forums Sponsor:

May 11th, 2012, 01:42 PM

CarlosinFL

Contributing User

Join Date: Aug 2011

Posts: 51

Time spent in forums: 10 h 8 m 48 sec
Reputation Power: 2

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

May 11th, 2012, 04:03 PM

r937

SQL Consultant

Dev Shed God 43rd Plane (26000 - 26499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,442

Time spent in forums: 3 Months 1 Week 2 Days 14 h 15 m 59 sec
Reputation Power: 4141

Quote:

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

May 13th, 2012, 09:25 PM

CarlosinFL

Contributing User

Join Date: Aug 2011

Posts: 51

Time spent in forums: 10 h 8 m 48 sec
Reputation Power: 2

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)

May 13th, 2012, 10:10 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,442

Time spent in forums: 3 Months 1 Week 2 Days 14 h 15 m 59 sec
Reputation Power: 4141

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?

Finding Max Value in a Row

Developer Shed Advertisers and Affiliates