Topic: SQL Query

[Canuck]

Ive been working on a site that uses php and mysql.  I collect hockey autographs through the mail, so the database stores ther person I sent to, the date sent/received, where I sent the request and how long it took.

The following query was to display the longest wait from a particular league.

Code: [Select]


SELECT f_name, l_name, sent, team, league, received, MAX(wait) as wait FROM ttm where league= 'NHL' group by league


It displays the right information for the MAX(wait), however it displays the wrong information for f_name, l_name etc, it is always the first entry in the database.  How do I grab the information associated with the result from MAX(wait)?

Hopefully that was clear.

Thanks!

Edit: If there is more than one result, how would I display all of them?

[hans]

Something like this should get the job done but may return a few results. You can tweak it to only return one based on some more info or a TOP 1 option.

Code: [Select]


SELECT f_name, l_name, sent, team, league, received, wait
FROM ttm
where league= 'NHL'
and wait = (select max(wait) from ttm where league = 'NHL')


[Canuck]

Thanks for the help tgm! works like a charm

[Mike]

Or...

Code: [Select]


SELECT f_name, l_name, sent, team, league, received, wait
FROM ttm
WHERE league='NHL'
ORDER BY wait DESC
LIMIT 1