查找列值在它们之间相等的顶部连续行

I need to get all the consecutive top row where a column value is equal between them

我需要获得列值相等的所有连续顶行

my table is:

我的表是：

CREATE TABLE [dbo].[Items](
  [Id] [int]  NOT NULL,
  [IdUser] [int] NOT NULL,
  [CreatedDate] [datetime] NOT NULL,
  [SomeData] nvarchar(50) NOT NULL);

and i want the top rows (ordered by Id desc) with the same IdUser

我希望顶行（由Id desc排序）与相同的IdUser

if table data is:

如果表数据是：

Id  IdUser  CreatedDate                 SomeData
--- ------- ------------------------    --------
1   1       2017-09-21T09:42:01.407Z    sdafsasfa
2   1       2017-09-21T09:42:01.407Z    sdafsasfa
4   2       2017-09-21T09:42:01.41Z     sdafsasfa
5   3       2017-09-21T09:42:01.41Z     sdafsasfa
7   3       2017-09-21T09:42:01.413Z    sdafsasfa
8   3       2017-09-21T09:42:01.413Z    sdafsasfa
9   10      2017-09-21T09:42:01.417Z    sdafsasfa
11  11      2017-09-21T09:42:01.417Z    sdafsasfa
12  2       2017-09-21T09:42:01.42Z     sdafsasfa
15  2       2017-09-21T09:42:01.42Z     sdafsasfa

I want :

我想要：

Id  IdUser  CreatedDate                 SomeData
--- ------- ------------------------    --------
12  2       2017-09-21T09:42:01.42Z     sdafsasfa
15  2       2017-09-21T09:42:01.42Z     sdafsasfa

if table data is:

如果表数据是：

Id  IdUser  CreatedDate                 SomeData
--- ------- ------------------------    --------
1   1       2017-09-21T09:42:01.407Z    sdafsasfa
2   1       2017-09-21T09:42:01.407Z    sdafsasfa
4   2       2017-09-21T09:42:01.41Z     sdafsasfa

I want :

我想要：

Id  IdUser  CreatedDate                 SomeData
--- ------- ------------------------    --------
4   2       2017-09-21T09:42:01.41Z     sdafsasfa

SqlFiddle

5 个解决方案

#1

you can try this query:

你可以试试这个查询：

select I.* 
from 
[dbo].[Items] I 
JOIN
(select top 1 Id, IdUser from [dbo].[Items] order by Id desc)I2
on I.Iduser=I2.Iduser
order by Id desc;-- this can be removed to remove ordering by Id Desc

updated fiddle link

更新小提琴链接

#2

You could use LAG and SUM() OVER() like this

您可以像这样使用LAG和SUM（）OVER（）

DECLARE @Items as Table 
(
  [Id] [int]  NOT NULL,
  [IdUser] [int] NOT NULL,
  [CreatedDate] [datetime] NOT NULL,
  [SomeData] nvarchar(50) NOT NULL
);

INSERT INTO @Items
(
    Id,
    IdUser,
    CreatedDate,
    SomeData
)
VALUES

( 1 , 1 ,getdate(),'sdafsasfa'),
( 2 , 1 ,getdate(),'sdafsasfa'),
( 4 , 2 ,getdate(),'sdafsasfa'),
( 5 , 3 ,getdate(),'sdafsasfa'),
( 7 , 3 ,getdate(),'sdafsasfa'),
( 8 , 3 ,getdate(),'sdafsasfa'),
( 9 , 10,getdate(),'sdafsasfa'),
( 11, 11,getdate(),'sdafsasfa'),
( 12, 2 ,getdate(),'sdafsasfa'),
( 15, 2 ,getdate(),'sdafsasfa')

;WITH temp AS 
(
    SELECT *,
         CASE 
            WHEN  lag(i.IdUser) over(ORDER BY i.Id) = i.IdUser THEN 0
            ELSE 1
         END as ChangingPoint          
    FROM @Items i
),
temp1 AS
(
    SELECT 
          *,
          sum(t.ChangingPoint) OVER(ORDER BY t.Id) as GroupId
    FROM temp t
)

SELECT TOP 1 WITH TIES
       t.Id,
       t.IdUser,
       t.CreatedDate,
       t.SomeData
FROM temp1 t
ORDER BY GroupId  DESC

See demo here: http://rextester.com/PHWWU96232

请参阅此处的演示：http：//rextester.com/PHWWU96232

#3

Assuming you want last rows with highest CreateDate and same IdUser then DENSE_RANK will help

假设您想要具有最高CreateDate和相同IdUser的最后一行，那么DENSE_RANK将有所帮助

SELECT id, iduser, CreatedDate, somedata
FROM (
    SELECT id, iduser, CreatedDate, somedata, 
           DENSE_RANK() OVER (ORDER BY CreatedDate desc, IdUser) ord
    FROM [dbo].[Items]) t
WHERE t.ord = 1

The equivalent SQL query is

等效的SQL查询是

SELECT *
FROM Items t1
WHERE NOT EXISTS (
         SELECT *
         FROM Items t2
         WHERE t2.createddate > t1.createddate or 
              (t2.createddate = t1.createddate and t2.iduser < t1.iduser)
      )

demo

演示

#4

Despite TriV's solution works fine I ended up using a modified Radim Bača's solution (his solution dont work as i need) because it is faster IMO

尽管TriV的解决方案工作正常，我最终使用改进的RadimBača的解决方案（他的解决方案不能正常工作），因为它更快的IMO

SELECT id, iduser, createddate, somedata
FROM Items t1
WHERE NOT EXISTS (
         SELECT 1
         FROM Items t2
         WHERE t2.id > t1.id and t2.iduser <> t1.iduser  );

SQLFiddle

#5

select I.* 
from 
[dbo].[Items1] I 
JOIN
(select top 1 Id, IdUser,CreatedDate from [dbo].[Items1] order by Id desc)I2
on I.CreatedDate=I2.CreatedDate
order by Id desc;-- this can be removed to remove ordering by Id Desc

#1