min/max - return entire row?

[ripken204]

i am having an issue with min/max. it is properly return the value i want but i need the entire row where that min/max value is to be returned. how would i do this?

for ex:
a tbl with name,age

i can get the min/max age but how would i get the name associated with it?

[TJ111]

This example is using PHP variables, just switch it out with whatever language you use.

SELECT * FROM tbl WHERE min<=$num AND max>=$num;

[ripken204]

what exactly is $num tho?
i want to make a comparison to tb1.age if we were to look at my example

[TJ111]

$num is just a variable, it could be any number.

Using the above statement, and returning the results to an array called "row" (again, using PHP variables just for example purposes).

$row; //an array of the row returned.
print $row['name']; //prints the value of "name"
print $row['age']; //would print the age returned

[ripken204]

how is $num helpful then if it can be anything?

let me give another example
lets do bank accounts

people have the ability to have multiple bank accounts
each person has their own id number
and i want to pull the row for the bank account with the least amount of money for everyone
there is an accounts db and a ppl db. the accounts are registered to ppl thru ppl.id

[ripken204]

anyone?

[TJ111]

SELECT* FROM tbl ORDER BY money ASC LIMIT 1;

[chazzy]

the only guaranteed way to get the entire row back in a query like this is to use a subquery, it allows the SQL to be fully independent.

Something like this should work.

select * from your_table where that_min_max_column = (select min(that_min_max_column) from your_table)


But realize that uniqueness is not guaranteed. without knowing more about the table structure, it's impossible to come up w/ a query that always returns 1 result. It's typically a bit dangerous to expect only one result back in this case.

[ripken204]

ok i will explain my database to you then, hopefully we can get this figured out

this is for track meet results

i have a results table with all the results from all the meets. this results table has columns:
id,athleteId,eventId,performance,meet_name,meet_date

so what i need to do is to sort by the performance, and pick the fastest time for each athleteId. then i want to be able to return the meet_name/meet_date. currently it is returning the fastest time but not pulling the correct row, the id numbers dont even match up..

[ripken204]

please someone, i have been trying to get this to work for over a month now and i have no been able to find a solution that works..

[mattyblah]

did chazzys solution not work? that's pretty much the only way to do what you're asking.