Search the highest number(in a row) in a MySQL table...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
The-Master
Forum Commoner
Posts: 45
Joined: Sun Aug 07, 2005 9:51 am
Location: Israel

Search the highest number(in a row) in a MySQL table...

Post by The-Master »

hi, if i have a table like this:
--------------------------
table_name:
id - age(int)
--------------------------

and i have these records:

--------------------------
id - age
1 - 34
2 - 45
3 - 19
--------------------------

how can i make that my script will find the highest "age" in the db records and if there are no records it will define a the var "$age" as '1'.(in this DB the highest "age" is 45...)

i am not asking you to write me a code i just want to know the function(or resource) that does this action...
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

You can either use the MAX() function or "ORDER BY age DESC LIMIT 1"

I'm not sure which is faster, probably MAX().
(#10850)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Just get yourself a (basic) book/tutorial/... on SQL...

Code: Select all

SELECT MAX(age) FROM persons
(The limit clause isn't standard sql... Admitted, one could argue that there no such thing as a standard query language...)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

timvw wrote: Admitted, one could argue that there no such thing as a standard query language...)
wikipedia wrote: 1986 SQL-86 SQL-87 First published by ANSI. Ratified by ISO in 1987.

1989 SQL-89 Minor revision.

1992 SQL-92 SQL2 Major revision.

1999 SQL:1999 SQL3 Added regular expression matching, recursive queries, triggers, non-scalar types and some object-oriented features. (The last two are somewhat controversial and not yet widely supported.)

2003 SQL:2003 Introduced XML-related features, window functions, standardized sequences and columns with auto-generated values (including identity-columns).
(See Eisenberg et al.: SQL:2003 Has Been Published.)
Still the SQL is being maintained by ISO and ANSI. Eventhough there are deviations and extensions of standard SQL but I think still there are basics which are adhered.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

As soon as one tries to restrict the maximum number of rows in a resultset, quite common imho, one already experiences how much the standard is really worth if you take into consideration all the dialect implementations for this functionality...

(I admit, since SQL2003, it has become possible with 'the standard')
The-Master
Forum Commoner
Posts: 45
Joined: Sun Aug 07, 2005 9:51 am
Location: Israel

Post by The-Master »

thanks for the help...
Post Reply