select a single distinct column in a table while others remain as it is

Question

hi i have the following table

P_id    Fname         Lname

1   vaibhav      shukla
2   davalesh      barak
2   sumo          barath
3   kushal        mehra

now i want a query which returns any of the following table

P_id     Fname       Lname

1   vaibhav      shukla
2   davalesh      barak
3   kushal        mehra

OR

P_id     Fname       Lname

1   vaibhav      shukla
2   sumo          barath
3   kushal        mehra

quick solution appreciated

Answer 1

select     t1.p_id
,          min(t2.lname) lname
,          min(t2.fname) fname
from       tab t1
inner join tab t2
on         t1.p_id = t2.p_id
where      t2.Lname = (
               select min(lname)
               from   tab t2
               where  t2.p_id = t1.p_id 
           )
group by   t1.p_id

Answer 2

Seems like you want to get Distinct on one column.

Hoping ur using SQL Server 2005 or Above

Select First_Name,Last_Name,P_Id
FROM
(
Select First_Name,Last_Name,P_Id

Row_Number() Over (Partition By P_Id Order By First_Name) As RNum

From Table1) T1
WHERE T1.RNum = 1

Idea is to assign distinct row number inside group PID and then get the one with RowNumber = 1

For SQl Server 2000

Select Distinct First_Name,Last_Name,P_ID
From Table1 T1
INNER JOIN 
(
   Select Min(First_Name) First_Name,P_ID

   From Table1 T1
   Group By P_ID
) T2 ON T1.P_ID = T2.P_ID AND T1.First_Name = T2.First_Name

Note: In case when two rows have same P_ID and First_Name and Different Last_Name it will return 2 rows. I am checking for that.

Another is way to create a temp table with Identity Column. Then instead of using min(First_Name) in inner query we will use min(Idenity) group by P_ID and in join we will use P_ID and identity for join.