Selecting top 1 records from multiple records of each day

Shreyas Kale 61 Reputation points
2022-09-22T04:38:33.067+00:00

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...?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,937 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,566 questions
0 comments No comments
{count} votes

Accepted answer
  1. CosmogHong-MSFT 23,951 Reputation points Microsoft Vendor
    2022-09-22T06:12:32.937+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. Olaf Helper 41,571 Reputation points
    2022-09-22T05:11:11.75+00:00

    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.

    0 comments No comments