如何根据不同列选择整行

Question

我在火花中这样做

cityId  PhysicalAddress      EmailAddress         ..many other columns of other meta info...   
1       b st                 [e-mail]   
1       b st                 [e-mail]   <- some rows can be entirely duplicates
1       a avenue             [e-mail]
2       c square             [e-mail]
2       d blvd               [e-mail]

这张表上没有主键，我想根据每个不同的cityId随机获取一行

例如，这是一个正确的答案

cityId  PhysicalAddress      EmailAddress        ..many other columns 
1       b st                 [e-mail]   
2       c square             [e-mail]

例如，这也是一个正确的答案

cityId  PhysicalAddress      EmailAddress       ..many other columns 
1       a avenue             [e-mail]
2       c square             [e-mail]

想到的一种方法是使用< code>group by。然而，这要求我在另一列上使用聚合函数。(比如min())。然而，我只想拉出一整行(不管是哪一行)。

Answer 1

我有Sql Server 2008 R2，但试图找到可以在其他DBMS上运行的方法。

create table contacts( cityId int, PhysicalAddress varchar(max), EmailAddress varchar(max) )

delete contacts
insert contacts( cityId, PhysicalAddress, EmailAddress )    /** ..many other columns of other meta info...  */  
values 
   ( 1, 'b st', '[e-mail]' )  
 , ( 1, 'b st', '[e-mail]' )                     /* some rows can be entirely duplicates */
 , ( 1, 'a avenue', '[e-mail]' )
 , ( 2, 'c square', '[e-mail]' )
 , ( 2, 'd blvd', '[e-mail]' )
 , ( 3, 'e circuit', '[e-mail]' )

-- using row_number()

with c as (
      select *, row_number() over (partition by cityId order by cityId) as seqnum
      from contacts
     )
    select * from c
    where seqnum = 1;


-- Add a new identity column

alter table contacts
    add id int identity(1,1)

select * from contacts where id in (select min(id) from contacts group by cityID)

-- Variation: Create a copy into a temp table and add an identity column
-- Note: It may not be possible to modify original table

select * into #contacts from contacts
alter table #contacts
    add id int identity(1,1)
select * from #contacts where id in (select min(id) from #contacts group by cityID)

我还尝试使用newid（）使用计算列，但我的兴奋是短暂的，因为当您将表连接到自身或在该表上使用子查询时，会为每个SELECT重新计算计算列，所以这不起作用。您不能使计算列＜code＞PERSISTED＜/code＞-这对于非确定性表达式（如＜code＞newid（）＜/code>）是不允许的，因为它每次在给定行上调用时都会返回不同的内容。

Answer 2

;WITH CTE AS
(
   SELECT *, ROW_NUMBER() OVER(PARTITION BY cityId ORDER BY cityId) AS RN
   FROM [TABLE_NAME]
) SELECT * FROM CTE WHERE RN = 1