Hi all I have the following query :
$query = "SELECT DISTINCT registration, series, operator, msn, ln, model, status, pic, piccopy FROM (SELECT * FROM (SELECT * FROM history where status = 'active' ORDER BY date asc) AS a GROUP BY ln) a2 GROUP BY msn ORDER BY ln asc";
this works - Kind of.... see here
its currently listing all airframes that have been or currently are active. I need it to only list CURRENTLY active airframes. This is because an airframe is scrapped or put into a museum for example - its classed as no longer active - e.g. preserved, the same for those that may be scrapped, but the above query still sees it as active. What can I do to prevent this showing aircraft that may falling into one of the following categories:
- parted out
- written off
however a grounded /stored/preserved airframe may be returned to service and become active again, so it needs to find the latest "active" and only display if its not been withdrawn after that date.
Im guessing its something simple where by it grabs the records for a certain airframe eg ET-AOP, and selects the last (latest by date aka newest date) sees if its active and if so displays it, if not then it wont. but how do I do this, its driving me mental