How do you obtain a single row per person from a multi-row result for each person

Question

A table has a row for each date an employee is at work, and gives a multi-row result when a query is run on a specific employee. To obtain information on the latest date worked, I use the MAX function and group by date and other relevant columns to get the latest data for the employee. I also use the TOP (1) function to get only one row. This works well if I am querying one employee only. What I would like to do, is to query a group of employees via their EmpNo, and get the latest date worked for each employee. Remembering that each employee queried will yield several records that ends up as one record after the grouping and Top (1) function, how do I query several employees and get just one record with the latest data for each employee?

The query I used to get the single latest record for each employee looks like this:

SELECT  TOP (1) EmpNo, Status, Title, MAX(DailyDate) AS Max
FROM            EmpTable
WHERE           EmpNo = 1234567
GROUP BY EmpNo, Status, Title, DailyDate
ORDER BY DailyDate DESC

I tried doing it this way but failed miserably. How else can I do the query to achieve this?

SELECT DISTINCT EmpTable.EmpNo, EmpTable.Status, EmpTable.Title, TEMP.Max
FROM   EmpTable INNER JOIN
                (SELECT    TOP (1) EmpNo, Status, Title, MAX(DailyDate) AS Max
                 FROM         EmpTable AS EmpTable_1
                 WHERE        (EmpTable_1.EmpNo = EmpNo)
                 GROUP BY EmpNo, EmpNo, Status, Title, DailyDate
                 ORDER BY DailyDate DESC) AS TEMP ON EmpTable.EmpNo = TEMP.EmpNo
WHERE   (EmpTable.EmpNo IN (240485, 491628, 587171, 329531, 261342, 241977, 213617, 154477, 142585, 279527, 409745, 290893, 191298, 197427, 156534, 147878))

Example of how the data is arranged in the table:

EmpNo   Status  Title       DailyDate
1       A       Programmer  4/22/2021
1       A       Programmer  4/21/2021
1       A       Programmer  4/20/2021
1       A       Programmer  4/19/2021
1       A       Programmer  4/16/2021
2       A       Analyst     4/22/2021
2       A       Analyst     4/21/2021
2       A       Analyst     4/20/2021
2       A       Analyst     4/19/2021
2       A       Analyst     4/16/2021
3       A       Secretary   3/31/2021
3       A       Secretary   3/30/2021
3       A       Secretary   3/29/2021
3       A       Secretary   3/26/2021
3       A       Secretary   3/25/2021

Result (showing most current dates per employee):

EmpNo   Status  Title       DailyDate
1       A       Programmer  4/22/2021
2       A       Analyst     4/22/2021
3       A       Secretary   3/31/2021

Based on the suggestion I got in the Stackoverflow answer below, I used the following SQL, but I got a listing of all 15 employees records shown above for EmpNos 1, 2 & 3, instead of the 3 records in the result set above.

SELECT EmpNo, Status, TITLE, DailyDate
FROM  (
        SELECT       EmpNo, Status, TITLE, DailyDate,
              ROW_NUMBER() OVER (PARTITION BY EmpNo, Status, TITLE, DailyDate ORDER BY DailyDate DESC) AS DateOrder
              FROM         SIF_Main
         ) TEMP
WHERE  DateOrder = 1
AND    EmpNo IN (1, 2, 3)

Your help will be greatly appreciated.

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — SMor, Apr 24 '21 at 23:25
Thanks, a mil for your codes. It's the first I am seeing this type of coding in SQL. I ran your suggested codes, but it listed every record on the EmpTable ignoring the list of EmpNos I included in the query. I edited my original post and added how the data is arranged in the table and my expected results. The EmpTable is arranged in DailyDate order, with the most current DailyDate listed first, and subsequent dates listed afterwards for each employer. What I need is the have one record per employee with the most current DailyDate listed once for each EmpNo included in my query. — Sast77, Apr 25 '21 at 12:51

dbCandan055 · Answer 1 · 2021-04-25T14:04:40.217

1

Firstly, 'order by' in subselect doesn't work. You should not write SQL in this way. Anyway, I'm sharing the script at under line in order that you can get latest data by each employee.

SELECT EmpNo, Status, Title, DailyDate
FROM (
    SELECT EmpNo, Status, Title, DailyDate, ROW_NUMBER() OVER (PARTITION BY EmpNo ORDER BY DailyDate DESC) AS DateOrder
    FROM EmpTable
) TMP1
WHERE DateOrder = 1;/* AND EmpNo IN (1, 2, 3, 4)    -- If you want filtering, you can use in this way*/

edited Apr 25 '21 at 14:04

answered Apr 25 '21 at 04:45

dbCandan055

126
3

Thanks, a mil for your code. It's the first I am seeing this type of coding in SQL. I ran your suggested codes, but it listed every record on the EmpTable ignoring the list of EmpNos I included to the query. I edited my original post and added how the data is arranged in the table and my expected results. The EmpTable is arranged in DailyDate order, with the most current DailyDate listed first, and subsequent dates listed afterwards for each employer. What I need is the have one record per employee with most current DailyDate listed once for each EmpNo included in my query. – Sast77 Apr 25 '21 at 10:33
1

I have recently edited to my code. Can you try it now? – dbCandan055 Apr 25 '21 at 14:02
This works perfectly. Thank you so much. I so appreciate getting to know this type of coding to solve a problem I have been struggling with for a long time, i.e. to get a single row per employee from a table where each employee have multiple rows of data, and being able to list the designated row for several employee all at once. Thanks again. – Sast77 Apr 26 '21 at 02:02

How do you obtain a single row per person from a multi-row result for each person

1 Answers1