从没有唯一标识的重复记录中获取第一条记录 [英] Get top first record from duplicate records having no unique identity

问题描述

我需要从下面给出的表中的每个重复记录集中获取第一行.我需要在视图中使用此查询

I need to fetch top first row out of each duplicate set of records from table given below. I need to use this query in view

请不要临时表，因为我已经通过添加标识列和最小函数并分组来完成它.我需要没有临时表或表变量的解决方案

please no temp table as I have already done it by adding identity column and min function and group by. I need solution without temp table or table variable

这只是示例数据.原始表中有 1000 条记录，我只需要前 1000 条的结果，所以我不能使用 distinct

This is just sample data. Original has 1000s of records in table and I need just result from top 1000, so I can't use distinct

我使用的是 SQL Server 2005

I am using SQL Server 2005

推荐答案

答案具体取决于前 1000 条不同"记录的含义.

The answer depends on specifically what you mean by the "top 1000 distinct" records.

如果你的意思是你想最多返回 1000 条不同的记录，不管表中有多少重复，那么写:

If you mean that you want to return at most 1000 distinct records, regardless of how many duplicates are in the table, then write this:

SELECT DISTINCT TOP 1000 id, uname, tel
FROM Users
ORDER BY <sort_columns>

如果您只想搜索表中的前 1000 行，并且可能返回的不同行远少于 1000 行，那么您可以使用子查询或 CTE 编写它，如下所示:

If you only want to search the first 1000 rows in the table, and potentially return much fewer than 1000 distinct rows, then you would write it with a subquery or CTE, like this:

SELECT DISTINCT *
FROM
(
    SELECT TOP 1000 id, uname, tel
    FROM Users
    ORDER BY <sort_columns>
) u

如果您不关心返回哪些记录，ORDER BY 当然是可选的.

The ORDER BY is of course optional if you don't care about which records you return.

这篇关于从没有唯一标识的重复记录中获取第一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！