SQL Server query to get first createdby value in a table

Question

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.

[`FIRST_VALUE`](https://learn.microsoft.com/en-us/sql/t-sql/functions/first-value-transact-sql?view=sql-server-ver16)? — Thom A, Oct 31 '22 at 17:09
`MIN` is exactly what you need. But you will also need a `group by`. — Sean Lange, Oct 31 '22 at 17:10
sorry guys i am trying to edit my question so its more clear. — Lazytitan, Oct 31 '22 at 17:11
Potential duplicate: https://stackoverflow.com/q/6841605/20091109 — Stuck at 1337, Oct 31 '22 at 17:20
Is your image that of your expected results, your sample dta, or a mix of both here? — Thom A, Oct 31 '22 at 17:22

score 2 · Answer 1 · answered Oct 31 '22 at 17:19

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

score 1 · Answer 2 · answered Oct 31 '22 at 17:24

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

SQL Server query to get first createdby value in a table

2 Answers2