CODEWITHSUNDEEP
sqlsql-servert-sql
giammin
giammin

Reputation: 18958

Find top consecutive rows where a column value is equal between them

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

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

Upvotes: 1

Views: 85

Answers (5)

piyush jain
piyush jain

Reputation: 113

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

Upvotes: 0

giammin
giammin

Reputation: 18958

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

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

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

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

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

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

Upvotes: 1

TriV
TriV

Reputation: 5148

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

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

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

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

Upvotes: 1

Related Questions