每个参考ID选择TOP 1

时间:2020-12-31 00:51:37

These are my tables simplified..

这些是我简化的表格..

person
( id [int],
  surname [varchar] (30),
  ref [int],
)

episode
(
  id [int],
  ref [int],
  typeId [int],
  startDate [datetime]
)

type
(
  typeId [int],
  typeName [varchar]
)

I want to select all the people who have more than 1 episode and the oldest episode started after 1 Jan 2013. I tried using Row_Number and partition but i'm using Sql Server 2005 and it didn't like Row_Number().

我想选择所有超过1集并且最早的一集在2013年1月1日之后开始的人。我尝试使用Row_Number和分区,但我使用的是Sql Server 2005,它不喜欢Row_Number()。

1 个解决方案

#1


3  

use:

having count(*)>1
and min(startDate) >'1 Jan 2013'

Edited: Below comment is right

编辑:以下评论是对的

#1


3  

use:

having count(*)>1
and min(startDate) >'1 Jan 2013'

Edited: Below comment is right

编辑:以下评论是对的