One of my contractors was supposed to write queries for a big project Im doing but disapeared (oh joy) so I am forced to "climb back into" the heavy sql part of this thing (normally I do the front end, java and some minor sql as needed). I will tell you my high powered sql is REALLY rusty and its showing today as I cant get anything back on the queries Im trying. Here's what Im TRYING to do:
I am pulling back rows of people (7 fields per row) from a main candidate table which has a unique ID per row. Then I need the max date from another table where multiple rows of comments are stored for a given candidate (to order the candidates in the output by "last updated") AND I need one field from a work experience table, it being the last job position listed in that table for the candidate, hence I have to get the postition for the max (end_date) of their work experience and then get THAT row's job title.
SO the pseudeocode should go like this: (at least I think!)
required fields from candidates (includes unique id)
max(last_updated) from comments (comment_can_id = candidates_id)
last_position from work_experience where job_end_date is MAX for the specific candidate_id (can_work_id = candidates_id)
order BY last_updated
I should note that the last_updated field for the comments is a timestamp and the job_end_date is a regular Date field
I havent done this nasty of a query in YEARS and BOY how we forget! Ive tried all possbile joins I could think of and am particularly frustrated with the group bys Postgres is requiring ..... got 10 books sitting here and a headache to beat the band! Can someone give me the kick in the head I seem to need (or at least an aspirin)?
thanks much ...... this guy has REALLY put me behind the 8-ball!