Selecting top 1 records from multiple records of each day

Question

I am using below SQL query to get all records of employee punch-in date from attendance system which is resulting multiple records of each employee of current date till past records up to 1st Jan 2022.

SELECT [TENETWORKID],[DeviceName], MIN(CAST([EDateTime] AS DATE)) AccessDate FROM [PowerBI].[dbo].[PowerBI_data]
WHERE CONVERT(VARCHAR(10), [EDateTime], 102) > CONVERT(VARCHAR(10), '2022-01-01 00:00:00', 102)
GROUP BY CAST([EDateTime] AS DATE),[TENETWORKID],[DeviceName]
ORDER BY [TENETWORKID],AccessDate

Result of above query is as below:

|TENETWORKID|DeviceName |AccessDate |
|ABC |6th Floor entrance|2022-01-01 |
|ABC |6th Floor entrance|2022-01-01 |
|XYZ |8th Floor entrance|2022-01-01 |
|XYZ |9th Floor entrance|2022-01-01 |
|ABC |7th Floor entrance|2022-01-02 |
|XYZ |6th Floor entrance|2022-01-02 |
|ABC |7th Floor entrance|2022-01-02 |
|XYZ |6th Floor entrance|2022-01-02 |

I need to able to return most recent unique TENETWORKID of each day from multiple records and Expected result is as below:

|TENETWORKID |DeviceName |AccessDate |
|ABC |6th Floor entrance|2022-01-01 |
|XYZ |8th Floor entrance|2022-01-01 |
|ABC |7th Floor entrance|2022-01-02 |
|XYZ |6th Floor entrance|2022-01-02 |

Can you please guide what needs to be changed in my query to achieve this result...?

Answer 1

Hi @Shreyas Kale
The reason why you got multiple records for single date per TENETWORKID is: you have extra GROUP BY column [DeviceName] which is no need.
Since one TENETWORKID could have different records with different DeviceNames in one single date, so if you GROUP BY CAST([EDateTime] AS DATE),[TENETWORKID],[DeviceName] you will got that issue.
Here is a solution using ROW_NUMBER:

;WITH CTE AS  
(  
 SELECT [TENETWORKID],[DeviceName],[EDateTime]  
       ,ROW_NUMBER()OVER(PARTITION BY CAST([EDateTime] AS DATE),[TENETWORKID] ORDER BY [EDateTime] ASC) AS RNum  
 FROM [PowerBI].[dbo].[PowerBI_data]  
)  
SELECT [TENETWORKID],[DeviceName],CAST([EDateTime] AS DATE) AccessDate  
FROM CTE  
WHERE RNum = 1  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Result of above query is as below:

We don't have your database/tables to reproduce it.
Please post table design as DDL, some sample data as DML statement and the expected result.

I need to able to return most recent unique TENETWORKID

How do you define "most recent"? Your data shows up only date and they are per TENETWORKID equal; so there can't be a most recent.

That's to less on information, please provide more details.