SQL Server query to get first createdby value in a table-CodePudding

emp_record_change_log_tbl has the following columns. I want the query to be like this, where "SQL code needed" will be the query to get the name of the person who entered the first record for a specific employee.

Select 
    change_id,
    Emp_ID,
    new salary,
    {sql code needed} as first_entered_by
from 
    emp_record_change_log_tbl

I know that at some point I will need a min function to first get the initial date.

But the result I am hoping for is to get John doe for employee 103 and Sarah Smith for employee 102.

CodePudding user response：

You can use 'CROSS APPLY' to do this.

SELECT DISTINCT t.Emp_ID, last_modifiedby, last_modifieddate
FROM emp_record_change_log_tbl t
CROSS APPLY (SELECT TOP 1 last_modifiedby, last_modifieddate
             FROM emp_record_change_log_tbl
             WHERE Emp_ID = t.Emp_ID
             ORDER BY last_modifieddate ASC) ca

If you have an Employee table with a PRIMARY KEY you don't need the DISTINCT.

SELECT t.Emp_ID, last_modifiedby, last_modifieddate
FROM employee t
CROSS APPLY (SELECT TOP 1 last_modifiedby, last_modifieddate
             FROM emp_record_change_log_tbl
             WHERE Emp_ID = t.Emp_ID
             ORDER BY last_modifieddate ASC) ca

CodePudding user response：

Instead of using MIN I would use subquery, TOP and ORDER BY. Here is how it would look for your example:

SELECT change_id, Emp_ID, NewSalary, 
(SELECT TOP(1) LastModifiedBy 
FROM emp_record_change_log_tbl 
WHERE Emp_Id = mainTable.Emp_Id 
ORDER BY LastModifiedDate) AS first_entered_by
FROM emp_record_change_log_tbl AS mainTable