Getting a MAX value out of a table

Anon

Hi;
I have a table that contains 2 fields:
FIELD_A is a program name
FIELD_B is a TIMESTAMP field

I want to extract these as a pair - I want to pull out the MAXIMUM value of FIELD_B and the program name it's related to:

i.e. if I have
program1 20000101010101
program2 20000202020202
program3 20000000000000

then I want a SELECT statement that will pull out the largest value of FIELD_B
and the value of FIELD_A that's on the same row: so in the example above I'd expect to get:

program2 20000202020202

I tried
SELECT MAX(FIELD_😎 from TABLE;
and that's fine - it gets the max value of FIELD_B with no trouble, but how do I get the value of FIELD_A out at the same time?

AHMA,
cheers,
Ben

Anon

You need to use a subquery:

select FIELD_A, FIELD_B from TABLE
where FIELD_B = (select max(FIELD_😎 from TABLE)

You may want to include DISTINCT in the main select statement if FIELD_B is non-unique, so you return only one row.

Note that this won't work in MySQL. You'd have to split it into two separate SQL statements, and hope that nobody updates your table in between your lookups.

Anon

Hey Ben,

I tried
SELECT MAX(FIELD_😎 from TABLE;
Put the fieldvalue in a var f.e. $MAX_B.

Just make place next query below it :'SELECT * FROM TABLE WHERE FIELD_B = $MAX_B'

And ur done!

Doin it in one query will give you serious problems.
This is the way I handle these things to keep my performance.

Arie

Anon

Nice Approach, Thanx!!!

Anon

The Query is rather simple, it reads as follows:

Select Field_A, MAX(FIELD_😎 from TABLE GROUP BY Field_A.

This will retrieve the Maximum value for Field_B associated with Field_A grouping it so that Field_a is associated with that maximum Value from Field_A.

Meaning you have

Field A ----- Field B

lunga ------- 12
lunga -------- 34
lunga -------- 66

Will only return

lunga ---- 66

Have fun

Anon

Yes, but if your data looks like this:

value1 1
value2 2
value3 3

then the query you suggest will get this result:

value1 1
value2 2
value3 3

This is probably not the result set "Ben" was looking for.

Anon

Or use the GROUP BY statement.

SELECT field_a,max(field_b) FROM table GROUP BY field_a;

Anon

Reading Ben's example, it's just what he was looking for.

Anon

Thanks for the help everyone..
I tried the GROUP_BY approach but that wouldn't work, so I went with 2 queries:

$query = mysql_query("
SELECT DISTINCT (FIELD_😎 from TABLE
");

while ($column = mysql_fetch_array($query)) {
$newest=$column["0"];
}

$query2 = mysql_query("
SELECT DISTINCT FIELD_A, FIELD_B from
TABLE where
FIELD_B = '$newest'
");

I may have been able to do the fetch_array bit in a different way, but this works just fine and allowed me to get the
FIELD_A/FIELD_B pair that had the MAX value
of FIELD_B.

Thanks again,
Ben