sql server - skip records based on columns condition -

i have question in sql server

table name : emp

id  |pid  |firstname| lastname  | level 1   |101  | ram     |kumar      | 3 1   |100  | ravi    |kumar      | 2 2   |101  | jaid    |balu       | 10 1   |100  | hari    | babu      | 5 1   |103  | nani    | jai       |44 1   |103  | nani    | balu      |10 3   |103  |bani     |lalu       |20 

here need retrieve unique records based on id , pid columns , records have duplicate records need skip.

finally want output below

id  |pid  |firstname| lastname  | level 1   |101  | ram     |kumar      | 3 2   |101  | jaid    |balu       | 10 3   |103  |bani     |lalu       |20 

i found duplicate records based on below query

select id,pid,count(*) emp group id,pid having count(*) >=2 

this query duplicated records 2 records need skip retrieve output

please tell me how write query achieve task in sql server.

since output based on unique id , pid not have duplicate value, can use count partition achieve desired result.

sql fiddle

sample data

create table emp     ([id] int, [pid] int, [firstname] varchar(4), [lastname] varchar(5), [level] int);  insert emp     ([id], [pid], [firstname], [lastname], [level]) values     (1, 101, 'ram', 'kumar', 3),     (1, 100, 'ravi', 'kumar', 2),     (2, 101, 'jaid', 'balu', 10),     (1, 100, 'hari', 'babu', 5),     (1, 103, 'nani', 'jai', 44),     (1, 103, 'nani', 'balu', 10),     (3, 103, 'bani', 'lalu', 20); 

query

select *  ( select *,rn = count(*) over(partition id,pid) emp ) emp rn = 1 

output

| id | pid | firstname | lastname | level | |----|-----|-----------|----------|-------| |  1 | 101 |       ram |    kumar |     3 | |  2 | 101 |      jaid |     balu |    10 | |  3 | 103 |      bani |     lalu |    20 |