当select子句中有两列时，如何选择一列的Top 1值 [英] How do I select Top 1 values of one column when there is two columns in the select clause

问题描述

我认为问题界限过于笼统。我有以下要求



有一个表有2列ID和PID。

其值如下：



ID PID

- ---

1 100

2 101
3 102

1 101

1 100

2 102

3 100
4 110



结果应该是两列都不应该有重复的ID。只应选择ID列的前1位。所以结果如下所示。



ID PID

- ---

1 100

2 101

3 102

4 110



请帮我这个。



谢谢和问候，

Mathi。

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.

推荐答案

选择不同*来自Tablename

Select distinct * from Tablename

大家好，

请找到以下解决方案



谢谢&此致，

Mathi。

Hi All,
Please find the below solution

Thanks & Regards,
Mathi.

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 

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

这篇关于当select子句中有两列时，如何选择一列的Top 1值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！