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?
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
CANDIDATE TABLE:
candidate_id_pf int
candidate_code int
candidate_surname Varchar(50)
candidate_address Varchar(200)
....
TIMESHEET_HEADER TABLE
timesheet_header_id_pf int
candidate_id_pf int
timesheet_header_no int
timesheet_header_date_paid date
timesheet_header_date_exported date
each candidate row has multiple timesheet_header rows.
I’m trying to get the oldest timesheet_header_exported date for each candidate.
If I use MAX in the subquery I get the correct result but I need to collect other columns in the same subquery and I dont wont multple rows.
7
Since you only select one of the timesheet_header columns, you can replace the LEFT JOIN with a correlated subquery in the SELECT list:
SELECT candidate.*,
(SELECT MAX(timesheet_header_date_exported)
FROM timesheet_header lexported
WHERE lexported.candidate_id_pf = candidate.candidate_id_pf)
FROM candidate
ORDER BY candidate_code