CODEWITHSUNDEEP
phpmysql
user1309158
user1309158

Reputation:

How do I get the highest number in a column?

My table named pictures has a column named pic and I'm wanting to get the highest number in that column. Each row has a unique number in pic and it goes up by 1 each row. But one or two rows won't have a number in the column and will have some text instead. Like grapes.

Here's an example of the table...

  TABLE: pictures
 ___________________
|  caption  |  pic  |
|-------------------|
|   some    |   1   |
|  random   |   2   |
|  thing    |   3   |
|   here    |grapes |
|___________________|

So, how would I get the highest number in the column pic which would be 3?

Column pic is a varchar.

Upvotes: 1

Views: 119

Answers (3)

Starx
Starx

Reputation: 79041

Use MAX() to get the maximum value available. Like this

SELECT MAX(pic) FROM `pictures` WHERE CONVERT(`pic`, SIGNED INTEGER) IS NOT NULL

Upvotes: 1

zerkms
zerkms

Reputation: 255105

SELECT MAX(CONVERT(pic, UNSIGNED INTEGER)) AS max_pic
FROM pictures
WHERE CONVERT(pic, UNSIGNED INTEGER) IS NOT NULL

The WHERE pic = pic + 0 condition is a trick that helps checking if it is a number value

Upvotes: 5

Dale
Dale

Reputation: 10469

Try this sql:

SELECT MAX(pic) FROM pictures

Depending on the column type you will get different results.

Upvotes: 0

Related Questions