Click here to Skip to main content
15,884,473 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
Hi,
I think the question line is too generic. I have the below requirement

There is a table with 2 columns ID and PID.
Which has values as below:

ID PID
-- ---
1 100
2 101
3 102
1 101
1 100
2 102
3 100
4 110

The result should have both the columns shouldn't have duplicates of ID. Only Top 1 of ID column should be selected. So the result should be like below.

ID PID
-- ---
1 100
2 101
3 102
4 110

Please help me in this.

Thanks & Regards,
Mathi.
Posted
Updated 13-Mar-14 2:35am
v2
Comments
Did you try anything?

Select distinct * from Tablename
 
Share this answer
 
Comments
vsrikanth87 13-Mar-14 8:44am    
or else u can use
Select distinct id ,pid from tablename
Hi All,
Please find the below solution

Thanks & Regards,
Mathi.

SQL
USE [MASTER]
GO
/****** Object:  Table [dbo].[testing_new]    Script Date: 03/13/2014 19:39:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[testing_new](
	[id] [int] NULL,
	[pid] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[testing_new] ([id], [pid]) VALUES (1, 100)
INSERT [dbo].[testing_new] ([id], [pid]) VALUES (2, 101)
INSERT [dbo].[testing_new] ([id], [pid]) VALUES (3, 102)
INSERT [dbo].[testing_new] ([id], [pid]) VALUES (1, 103)
INSERT [dbo].[testing_new] ([id], [pid]) VALUES (1, 104)
INSERT [dbo].[testing_new] ([id], [pid]) VALUES (2, 105)
INSERT [dbo].[testing_new] ([id], [pid]) VALUES (4, 106)



;with a as
(
SELECT [ID],[PID], 
rn = row_number() over (partition by [ID] order by ID)
FROM testing_new
)
select 
[ID],[PID] from a
where rn = 1 
 
Share this answer
 
SQL
SELECT ID,PID
FROM
(SELECT  ID,PID, ROW_NUMBER() OVER (PARTITION BY ID,PID ORDER BY ID) AS RowNUM FROM Mytable) AS MT
WHERE MT.RowNUM =1
 
Share this answer
 
Comments
Mathi2code 17-Mar-14 0:01am    
This Query resolved but in partition by need to mention only ID. I have that solution pasted below.
Thanks Prakash

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900