MAX() function help

[pmsbony]

I am putting together a query to pull out a status report from our database.

One of the requirements is the current status of a job. This status is held in a table (searchstatushistory) each job has multiple status entries against it and I need to pull the most recent status ID from the table, which then links to another table holding the status descriptions for each ID.

essentially the shortened (all criteria not relevant to this issue removed for simplicity)query is:

select ss_status
from
searchstatus
inner join searchstatushistory
on searchstatus.ss_status_id = searchstatushistory.srch_ss_status_id

where
srch_id = '1234'
"This is a specific job I want the current status of"

and ?


the ? is the bit I am confused by how do I limit the query to only show the most recent instance for each srch_id that is provided?

any help gratefully recieved

 

[pmanivas]

select max(searchstatus.ss_status_id), ss_status
from
searchstatus
inner join searchstatushistory
on searchstatus.ss_status_id = searchstatushistory.srch_ss_status_id
group by searchstatus.ss_status_id