How to mimic Microsoft Access's FIRST and LAST clauses on SQL Server 2k, 7.0 and 6.5
Suppose I have the following query run against Pubs database
SELECT employee.emp_id,
employee.fname,
employee.minit,
employee.lname,
employee.job_id,
jobs.job_desc,
employee.job_lvl,
employee.pub_id,
employee.hire_date
FROM employee
INNER JOIN
jobs
ON employee.job_id = jobs.job_id
ORDER BY jobs.job_desc,
employee.hire_date DESC
If I want to get only the record of the most recent hired (newest) employee (Microsoft Access's LAST clause) for each job, I would implement it on SQL server the following way
SELECT employee.emp_id,
employee.fname,
employee.minit,
employee.lname,
employee.job_id,
jobs.job_desc,
employee.job_lvl,
employee.pub_id,
employee.hire_date
FROM employee
INNER JOIN
jobs
ON employee.job_id = jobs.job_id
WHERE NOT EXISTS(
SELECT employee.emp_id
FROM employee AS employee_2
WHERE employee_2.job_id = employee.job_id AND
employee_2.hire_date > employee.hire_date
)
ORDER BY jobs.job_desc
If I want to get only the record of the first hired (oldest) employee (Microsoft Access's FIRST clause) for each job, I would implement it on SQL server the following way
SELECT employee.emp_id,
employee.fname,
employee.minit,
employee.lname,
employee.job_id,
jobs.job_desc,
employee.job_lvl,
employee.pub_id,
employee.hire_date
FROM employee
INNER JOIN
jobs
ON employee.job_id = jobs.job_id
WHERE NOT EXISTS(
SELECT employee.emp_id
FROM employee AS employee_2
WHERE employee_2.job_id = employee.job_id AND
employee_2.hire_date < employee.hire_date
)
ORDER BY jobs.job_desc
What do you think of implementing these features this way? Drop me a line and let me know about.