How to correctly use select top 1 inside a inner join of an update statement in T-SQL.

And the answer is: don’t use select top 1, instead use the row_number() function and the OVER clause.

Lets assume we have a table structure like:

 

 

Table [Observation]

Multiple observations during a TimeSpan on 1 barcode are collected into 1 [Observation] record.

 

if object_id('[dbo].[Observation]') is null
begin
create table [dbo].[Observation]
(
[Id]                int not null identity(1,1)  constraint PK_Observation_Id primary key,
[Barcode]            varchar(max) not null,
[Postcode]        varchar(max) not null,
[Date]            date not null,
[Count]            int not null,
[TimeSpanId]        int not null constraint FK_Observation_TimeSpanId foreign key references TimeSpan(Id)
)
end
go
if not exists(select top 1 1 from [Observation])
begin
insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN10000','4000AA','2012-06-18',1,1)
insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN10001','4000AA','2012-07-02',1,1)
insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN20000','4000AA','2012-05-29',2,2)
insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN30000','4000AA','2012-06-18',2,3)
end

Table [TimeSpan]

The table TimeSpan contains timespans of 2 hours.

if object_id('[dbo].[TimeSpan]') is null
begin
create table [dbo].[TimeSpan]
(
[Id]                int not null identity(1,1)  constraint PK_TimeSpan_Id primary key,
[Name]            varchar(max),
[Start]            time(7),
[End]                time(7),
[IsMostImportant] int not null default(0)
)
end
go
if not exists(select top 1 1 from [TimeSpan])
begin
insert into [dbo].[TimeSpan]([Name],[Start],[End],[IsMostImportant]) values ('07:00 - 09:00','07:00:00','09:00:00',0)
insert into [dbo].[TimeSpan]([Name],[Start],[End],[IsMostImportant]) values ('10:00 - 12:00','10:00:00','12:00:00',0)           
insert into [dbo].[TimeSpan]([Name],[Start],[End],[IsMostImportant]) values ('13:00 - 15:00','13:00:00','15:00:00',0)
end

The most important TimeSpan

The first TimeSpan with the most- and youngest observations.

 

Update Query

The column [IsMostImportant] can be updated by using the following query:

declare    @DateNow date
set @DateNow = '2012-07-12'
set nocount on                                -- SET NOCOUNT ON, added to prevent extra result sets from interfering with SELECT statements.
set datefirst 1                               -- Monday is first day of the week.
update [TimeSpan]
set [IsMostImportant] = 1
from [TimeSpan] ts
inner join
(
select
ob3.[RowNumber],
ob3.[TimeSpanId]
from
(
select
row_number() over(partition by ob2.[Postcode] order by ob2.[Count] desc, ob2.[DateDiffInDays] asc) as [RowNumber],
[Count],
[DateDiffInDays],
[Postcode],
[TimeSpanId]
from 
(
select
sum(ob.[Count]) as [Count],
sum(datediff(day, ob.[Date], @DateNow) * ob.[Count]) as [DateDiffInDays],
ob.[Postcode],
ob.[TimeSpanId]
from Observation ob
inner join TimeSpan ts on ob.TimeSpanId = ts.Id
group by ob.[Postcode], ob.[TimeSpanId]
) ob2
) ob3
where ob3.[RowNumber] = 1
) ob4
on ts.[Id] = ob4.[TimeSpanId]

 

Result

image

Leave a Reply

Your email address will not be published. Required fields are marked *