SQL – select greatest value Feb16 '05

On a few occasions recently, I’ve come across a situation where I needed to pull the greatest value from an integer, date, or time column, in SQL.

In other words, I just want the largest number, or the most recent date or time.

My solution was simply:

SELECT [field] FROM [table] ORDER BY [field] DESC LIMIT 1

This seems to work fine, but then I noticed a few functions in SQL, that may also give me the same results.

It turns out they are not working as intended.

GREATEST function

Introduced in MySQL 3.22.5, GREATEST is a function that returns the largest of included arguments, as such:

SELECT GREATEST(2,3,1)

The query above will return 3.

My question is... how do I use the GREATEST function to compare ALL values of a particular field? In other words, I don’t want to supply ALL the values in the parenthesis – I want SQL to grab all the values itself, and then provide me with the greatest one.

Categories: SQL

Add Feedback (view all)

Leave feedback

Feedback

Input format: The editor controls below will assist with Markdown syntax.

Status

Sub-status

Your info

I am not sure what version of SQL you are using, but in T-SQl, you use the "MAX()" function, like this: SELECT max(myColumn) myMaxValue FROM ... Read more.

Thank you so much for the "GROUP BY" tip -- I was making myself crazy with the MAX function. I just need to use the "GROUP BY" on the other column ... Read more.

matthom is published and produced by Matt Thommes - an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from Chicago. Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.

Contact Matt

Similar Entries

Stats

1584 unique visits since August 2008

Syndicate

Advertisements