I have a table where Employees can check assets and each of their checks is recorded in a table.
Now I want to extract the 5 last checked Asset_Ids for a given employee. He might have checked the same asset more than once, each check is recorded in a table -EmployeeLog-
Basically my table has 3 columns :
| Employee_ID | DateChecked | Asset_ID |
|---|---|---|
| 1 | 06/10/2021 10:56:22 | 1 |
| 1 | 06/10/2021 14:58:25 | 1 |
| 1 | 02/10/2021 13:56:22 | 2 |
| 1 | 06/10/2021 08:56:22 | 1 |
| 1 | 04/10/2021 03:56:22 | 1 |
| 1 | 06/10/2021 02:56:22 | 3 |
| 1 | 06/10/2021 15:56:22 | 2 |
| 1 | 03/10/2021 03:56:22 | 5 |
| 1 | 06/10/2021 03:56:22 | 5 |
I have below query which lists Asset_IDs by check date, but I have to apply distinct and top 5 to this list which I couldn't, any help appreciated.
SELECT Asset_ID
FROM EmployeeLog
WHERE EmployeeID = @Emp_ID AND Asset_ID != '00000000-0000-0000-0000-000000000000'
ORDER BY EmployeeLog.DateChecked Desc
CodePudding user response：
You can do a GROUP BY, and order by first (or last) date.
SELECT Asset_ID
FROM EmployeeLog
WHERE EmployeeID = @Emp_ID AND Asset_ID != '00000000-0000-0000-0000-000000000000'
GROUP BY Asset_ID
ORDER BY MIN(EmployeeLog.DateChecked) Desc
To get the first 5 rows only, do SELECT TOP 5 Asset_ID etc.