通过多列SQL Server一组一组地选择1行

I have a table with ids,versions,timestamp and I need to get the latest version for each id where the number of versions in more than 1.

我有一个包含id、版本、时间戳的表，我需要为每个id获取最新版本，其中版本数大于1。

For example

例如

123 1.5 2015-03-28 08:21:04.563
123 1.4 2015-02-28 08:21:04.563
234 1.5 2015-06-28 08:21:04.563
234 1.4 2015-05-28 08:21:04.563
234 1.3 2015-04-28 08:21:04.563
345 1.5 2015-01-28 08:21:04.563

In the above data id 123 and 234 have more than 1 version.I need to get the max(timestamp) along with the id and version

在上面的数据id 123和234中有一个以上的版本。我需要得到max(时间戳)以及id和版本

Output

输出

123 1.5 2015-03-28 08:21:04.563
234 1.5 2015-06-28 08:21:04.563

I have the following sql that will give me 1 row for each id,however I need to exclude ids with only 1 version.

我有下面的sql，它将为每个id提供1行，但是我需要排除只有1个版本的id。

 ;with versions as
(
    select  id, 
            version, 
            row_no = row_number() over (partition by id order by max(dt_create) desc), 
            dt_create = max(dt_create)
    from table_name
    group by id, version
)
select id,version,row_no, dt_create
from versions
where row_no=1
order by id

3 个解决方案

#1

You don't need an aggregate in the row_number. Also, you would want to use the IN clause to limit the ID's you stated.

在row_number中不需要聚合。此外，您还希望使用IN子句来限制您声明的ID。

As a side note, row_number is much faster than aggregations which is used in other answers. Good choice of you in the original query.

作为补充说明，row_number比在其他答案中使用的聚合要快得多。您在原查询中选择得很好。

 ;with versions as
(
    select  id, 
            version, 
            row_no = row_number() over (partition by id order by dt_create desc) 
    from table_name
)
select id,version,row_no, dt_create
from versions
where row_no=1 and id in (select id from versions where row_no > 1)
order by id

#2

This should do it:

这应该这样做:

    ;with mycte as (
select 123 as ID, 1.5 as version, '2015-03-28 08:21:04.563' as timestamp
union all 
select 123, 1.4, '2015-02-28 08:21:04.563'
union all 
select 234, 1.5, '2015-06-28 08:21:04.563'
union all 
select 234, 1.4, '2015-05-28 08:21:04.563'
union all 
select 234, 1.3, '2015-04-28 08:21:04.563'
union all 
select 345, 1.5, '2015-01-28 08:21:04.563'
)

Select ID
, max(version) as version
, max(timestamp) as timestamp
,count(*) as total
 from mycte
 group by ID
 having count(*) >1

#3

Your query is close. Add HAVING clause to exclude records that only have one version

您的查询是关闭。添加have子句以排除只有一个版本的记录

;with versions as
(
    select  id, 
            version, 
            row_no = row_number() over (partition by id order by max(dt_create) desc), 
            dt_create = max(dt_create)
    from table_name
    group by id, version
    having COUNT(*) > 1
)
select id,version,row_no, dt_create
from versions
where row_no=1
order by id

#1