Grouping with partition and over in TSql

Question

I have a simple table

CREATE TABLE [dbo].[Tooling](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Status] [int] NOT NULL,
[DateFinished] [datetime] NULL,
[Tooling] [nvarchar](50) NULL,
[Updated] [datetime] NULL,

 ) ON [PRIMARY]

with following values

SET IDENTITY_INSERT [dbo].[Tooling] ON 

GO
INSERT [dbo].[Tooling] ([Id], [Name], [Status], [DateFinished], [Tooling], [Updated]) VALUES (1, N'Large', 0, NULL, NULL, CAST(N'2015-05-05 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Tooling] ([Id], [Name], [Status], [DateFinished], [Tooling], [Updated]) VALUES (2, N'Large', 1, NULL, N'1', CAST(N'2015-05-10 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Tooling] ([Id], [Name], [Status], [DateFinished], [Tooling], [Updated]) VALUES (3, N'Small', 0, NULL, N'2', CAST(N'2015-05-11 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Tooling] ([Id], [Name], [Status], [DateFinished], [Tooling], [Updated]) VALUES (4, N'Large', 2, NULL, N'1', CAST(N'2015-05-12 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Tooling] ([Id], [Name], [Status], [DateFinished], [Tooling], [Updated]) VALUES (5, N'Large', 2, NULL, N'2', CAST(N'2015-05-12 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Tooling] ([Id], [Name], [Status], [DateFinished], [Tooling], [Updated]) VALUES (6, N'Large', 1, NULL, N'1', CAST(N'2015-05-13 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Tooling] ([Id], [Name], [Status], [DateFinished], [Tooling], [Updated]) VALUES (7, N'Large', 1, NULL, N'2', CAST(N'2015-05-14 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Tooling] ([Id], [Name], [Status], [DateFinished], [Tooling], [Updated]) VALUES (8, N'Small', 1, CAST(N'2015-05-15 00:00:00.000' AS DateTime), N'2', CAST(N'2015-05-15 00:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[Tooling] OFF

I want to create a view of the table that looks like its an entirely own table.

SELECT         Id, Name, Status, DateFinished
FROM          Tooling t order by t.id

If i run that I would like that the records with id 5 and 7 should be excluded since they dont change in the selected set from the previous row. I had an idea to solve this by using, ROW_NUMBER() over partition And by using group by but thats seems to incorrect(Could not to get it to work at all)

How do I group it when the should be grouped on that the value change and not the value it contains? Any suggestions to solve this? My end goal is also try to convert this to linq to entities...

Id is definetly different by design. Do you mean Name, Status, DateFinished are not changed? You also need to define the order - by id or what? — Serg
– Serg, Commented Jun 8, 2016 at 11:50

Serg · Accepted Answer · 2016-06-08 12:04:48Z

1

OK, supposing it's ordered by id

select * 
from (
  select *, rng = row_number() over (partition by grp order by id)
  from (
    select *, grp = row_number() over (order by id) - row_number() over (partition by Name, Status, DateFinished order by id)
    from tooling ) g
  ) gn
where rng = 1
order by id

answered Jun 8, 2016 at 12:04

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jerker Pihl Over a year ago

Really nice. Did exactly what I wanted,

Serg Over a year ago

Note you've got it quckly, and that's totally your merit. The question containing sample data script is pleasure to answer.

Collectives™ on Stack Overflow

Grouping with partition and over in TSql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related