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.

6 Comments

  • Wouldnt this work better



    Newest employee&gt;



    SELECT TOP 1 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



    Oldest employee&gt;



    SELECT TOP 1 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

  • Guy,



    Unfortunately, you did not let your email address so that I also could send you an email. Hope you come back and see my reply here.



    My queries were done based on tables from Pubs database, which is a sample SQL server database. That said, please run my queries and yours and you'll see they produce different results. Mine returns the newest and the oldest employee (based on hire_date) FOR EACH job_id. Yours returns the newest and the oldest employee FOR THE WHOLE company. In spite of it, thanks a bunch for the feedback.

  • Guy,



    I just run your queries and realized they are returning the newest and the oldest (based on hire_date) employees for the &quot;Acquisitions Manager&quot; job and I figured out it is because jobs.job_desc is part of the ORDER BY clause.

  • the technique used for this is called a correlated subquery. You might want to have a read about them.

  • Matthew,



    Thanks for the tip.

  • Estou retribuindo a visita que voc&#234; fez ao meu espa&#231;o na internet.

    Infelizmente o meu ingl&#234;s &#233; muito fraco(rsrsrs).

    Hoje em dia &#233; mais que necess&#225;rio falar outros idiomas, mas estou sem tempo...

    Paz e bem!

Comments have been disabled for this content.