how to get distinct RefNId raw data means All column record for distin

Question

i want to top 1 data from each distinct RefNId

Tuhin Paul · Answer

You can also use CTE that can make the query easier to read and understand. Here's an example WITH CTE AS ( SELECT Id, RefNId, NType, RoleId, DeptID, ROW_NUMBER() OVER (PARTITION BY RefNId ORDER BY Id) AS RowNum FROM YourTableName ) SELECT Id, RefNId, NType, RoleId, DeptID FROM CTE WHERE RowNum = 1;

Tuhin Paul · Answer

Thanks Naimish for posting,but there are a few ways that this query could be improved: 1. Instead of using ROW_NUMBER() to select the first occurrence of each group, you could use TOP 1 to select only the top row for each RefNId group. This can simplify the query and potentially improve performance. 2. If the table has a large number of rows and the RefNId column is not indexed, adding an index on this column can significantly improve query performance. 3. The data types used for the Id, RefNId, NType, RoleId, and DeptID columns are appropriate for the data being stored. Using smaller data types where possible can also improve query performance.

Naimish Makwana · Answer

Hello Sandeep, Use below query, SELECT Id, RefNId, NType, RoleId, DeptID FROM ( SELECT Id, RefNId, NType, RoleId, DeptID, ROW_NUMBER() OVER (PARTITION BY RefNId ORDER BY Id) AS RowNum FROM YourTableName ) subquery WHERE RowNum = 1 Thanks Naimish Makwana

Tuhin Paul · Answer

@Anandu G Nath The given SQL query is using a Common Table Expression (CTE) to assign a row number to each row based on the RefNId partition and ordering by Id. The final result selects only the rows where RowNum is equal to 1. This is a common pattern used to retrieve the first row for each group within a partition. Verify that the indexes are being used appropriately by examining the execution plan. You may need to adjust or add indexes based on the characteristics of your data and query patterns. something similar to the image shared.

Anandu G Nath · Answer

WITH CTE AS ( SELECT Id, RefNId, NType, RoleId, DeptID, ROW_NUMBER() OVER (PARTITION BY RefNId ORDER BY Id) AS RowNum FROM YourTableName ) SELECT Id, RefNId, NType, RoleId, DeptID, RowNum FROM CTE WHERE RowNum = 1;