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

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

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;
 

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.

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