According to all the documentation I have read this should work on most sql databases. I am running SQLAnywhere and it’s docs say this should work.
I am aware that I could run this as a normal subquery from within the SELECT section but I need to get multiple columns (I have cut this down for simplicity).
Adding ‘TOP 1’ to a subquery in a LEFT JOIN to get only the single row wont return any results for me. Running the subquery on it’s own works perfectly. I have also tried using the LIMIT key word with the same nil results.
Any suggestions please?
<code>SELECT candidate.*, timesheet_header_date_exported
FROM candidate
LEFT JOIN (SELECT TOP 1 timesheet_header.candidate_id_pf, timesheet_header_date_exported
FROM timesheet_header
ORDER BY timesheet_header_date_exported DESC) lexported ON (lexported.candidate_id_pf = candidate.candidate_id_pf)
ORDER BY candidate_code
</code>
<code>SELECT candidate.*, timesheet_header_date_exported
FROM candidate
LEFT JOIN (SELECT TOP 1 timesheet_header.candidate_id_pf, timesheet_header_date_exported
FROM timesheet_header
ORDER BY timesheet_header_date_exported DESC) lexported ON (lexported.candidate_id_pf = candidate.candidate_id_pf)
ORDER BY candidate_code
</code>
SELECT candidate.*, timesheet_header_date_exported
FROM candidate
LEFT JOIN (SELECT TOP 1 timesheet_header.candidate_id_pf, timesheet_header_date_exported
FROM timesheet_header
ORDER BY timesheet_header_date_exported DESC) lexported ON (lexported.candidate_id_pf = candidate.candidate_id_pf)
ORDER BY candidate_code
New contributor
markplatt is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.