Luciano Evaristo Guerche

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.