Hi there,
I have a table of health and safety inspections by organisation. most organisations will have had more than one H&S inspection. I would like to return the latest H&S result for each organisation. I understand that grouping and sorting the columns by date will get work as well, but i'd like to a SQL expression to do this, so far i can return the latest visit_date for each organisation, but what i want is to return the H&S status.
here's the SQL that am currently using.
(select max(DBA.HEALTH_AND_SAFETY_VISIT.Visit_Date)
from DBA.HEALTH_AND_SAFETY_VISIT
where ENTITY.customer_ref = DBA.HEALTH_AND_SAFETY_VISIT.customer_ref)
how can I return the latest visit_status based on the latest date that is recorded on HEALTH_AND_SAFETY_VISIT table? I am using a sybase db environment.
Many thanks
I have a table of health and safety inspections by organisation. most organisations will have had more than one H&S inspection. I would like to return the latest H&S result for each organisation. I understand that grouping and sorting the columns by date will get work as well, but i'd like to a SQL expression to do this, so far i can return the latest visit_date for each organisation, but what i want is to return the H&S status.
here's the SQL that am currently using.
(select max(DBA.HEALTH_AND_SAFETY_VISIT.Visit_Date)
from DBA.HEALTH_AND_SAFETY_VISIT
where ENTITY.customer_ref = DBA.HEALTH_AND_SAFETY_VISIT.customer_ref)
how can I return the latest visit_status based on the latest date that is recorded on HEALTH_AND_SAFETY_VISIT table? I am using a sybase db environment.
Many thanks