Hi, say the table has these films records in table called BOXOFFICE
I want latest results picked out of this:
Code:
FILM Updated Week Result
=======================================================
Indiana Jones 5/30/2008 1 Hit
Indiana Jones 6/7/2008 2 Hit
Hancock 7/4/2008 1 Hit
Hancock 7/11/2008 2 Average
Titanic 12/25/1997 1 Hit
Titanic 2/25/1998 10 Superhit
Titanic 3/25/1998 14 Blockbuster
So want to get DISTINCT film records based on MAX (updated) ie latest results; so need to return these three from above:
Code:
Indiana Jones 6/7/2008 2 Hit
Hancock 7/11/2008 2 Average
Titanic 3/25/1998 14 Blockbuster
[sql]Select film, max(updated), week, result from box office
group by film, week, result [/sql]
I don't think is the correct query.
What might work? Its a long problem I've had in sql. How to select an entire record, based on one of its fields being the min/max for that field among all the records. Thanks.