get the last vacation record only for all employees

How could I get the last vacation record only for all employees?
Table name : TBPRSVAC
Columns: vac_emp_no, vac_vac_type, vac_start_date, vac_end_date, vac_spend_place

You can use row_number() over() to arrive at a "most recent" row per employee, like this:

SELECT
    vac_emp_no
  , vac_vac_type
  , vac_start_date
  , vac_end_date
  , vac_spend_place
FROM (
    SELECT
        vac_emp_no
      , vac_vac_type
      , vac_start_date
      , vac_end_date
      , vac_spend_place
      , ROW_NUMBER() OVER (PARTITION BY vac_emp_no ORDER BY vac_end_date DESC) AS rn
    FROM TBPRSVAC
    ) vac
WHERE rn = 1

Open in new window

But depending on context, AND database, you might find that using an apply operator works for this, e.g.

SELECT
    emp.*
  , vac.vac_vac_type
  , vac.vac_start_date
  , vac.vac_end_date
  , vac.vac_spend_place
FROM employee emp
OUTER APPLY (
    SELECT TOP (1)
        vac_vac_type
      , vac_start_date
      , vac_end_date
      , vac_spend_place
    FROM TBPRSVAC vac
    WHERE emp.emp_no = vac.vac_emp_no
    ORDER BY
        vac_end_date DESC
    ) vac

Open in new window

Notes:
you may also want to use [vac_start_date] in the ordering of either row_number() or within the apply
outer apply is used in case there is no vacation record (it acts like a left join)
the queries above both work in SQL Server. The first query will work in any db supporting row_number()

NBNB: please nominate which database you actually use because "SQL" by itself doesn't narrow down the choices enough.

SELECT TBPRSVAC.* FROM TBPRSVAC INNER JOIN ( SELECT vac_emp_no, Max(vac_Start_Date) as MaxStart FROM TBPRSVAC Group by vac_Emp_No ) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.vac_Start_Date

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 52

PortletPaulEE Topic AdvisorCommented: 2018-09-09

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 53

Dale FyeOwner, Dev-Soln LLCCommented: 2018-09-09

Or you might try:

I know this will work to return the recordset, but it may not be updateable, depending on the database you are using.

Mohammad Alsolaimanapplication programmerAuthor Commented: 2018-09-11

PortletPaul
I had try both solutions, and they were awesome .

Dale Fye
I had encountered this error
Invalid column name 'vac_Start_Date'.
It was pointing to line Number 7
) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.vac_Start_Date

Dale FyeOwner, Dev-Soln LLCCommented: 2018-09-11

That should have been:

) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.MaxStart

Dale Fye
It works now, but there is a lot of repeat. I mean it bring the last vacation for each employee, and repeat it several times.

thanks to both of u

get the last vacation record only for all employees

Who is Participating?