0

Lets say I have a table with columns:

Name City Date Value
John NewYork 2022-01-02 10
John NewYork 2022-01-03 20
Mark NewYork 2022-01-01 30
Mark NewYork 2022-01-03 40

now main query qould be:

select Name, City, Date, Value from MyTable where Date = '2022-01-03'

So I add date to filter and get result, easy.

Now I would like to add value from last existing date for the same object, so I need to use self join:

select MyTbale.Name, MyTbale.City, MyTbale.Date, MyTbale.Value, B.Value as PrevValue from MyTable A
LEFT JOIN MyTbale B on A.Name = B.Name and A.City = B.City and B.Date = (select max(Date) from MyTbale where Name = A.Name and A.City = B.City and Date < A.Date)
where Date = '2022-01-03'

So the result would be:

Name City Date Value PrevValue
John NewYork 2022-01-03 20 10
Mark NewYork 2022-01-03 40 30

Is there any other way to do that?

1 Answer 1

2
CREATE TABLE [dbo].[MyTable](
    [name] [varchar](50) NULL,
    [city] [varchar](50) NULL,
    [date] [date] NULL,
    [value] [int] NULL
) ON [PRIMARY]
INSERT [dbo].[MyTable] ([name], [city], [date], [value]) VALUES (N'John', N'NewYork', CAST(N'2022-01-02' AS Date), 10)
INSERT [dbo].[MyTable] ([name], [city], [date], [value]) VALUES (N'John', N'NewYork', CAST(N'2022-01-03' AS Date), 20)
INSERT [dbo].[MyTable] ([name], [city], [date], [value]) VALUES (N'Mark', N'NewYork', CAST(N'2022-01-01' AS Date), 30)
INSERT [dbo].[MyTable] ([name], [city], [date], [value]) VALUES (N'Mark', N'NewYork', CAST(N'2022-01-03' AS Date), 40)


select * from MyTable
name city date value
John NewYork 2022-01-02 10
John NewYork 2022-01-03 20
Mark NewYork 2022-01-01 30
Mark NewYork 2022-01-03 40
select
m.*
,LAG(m.value) OVER (ORDER BY m.name,m.date) as 'PrevValue'
from myTable m
name city date value PrevValue
John NewYork 2022-01-02 10 null
John NewYork 2022-01-03 20 10
Mark NewYork 2022-01-01 30 20
Mark NewYork 2022-01-03 40 30
select * from (
select
m.*
,LAG(m.value) OVER (ORDER BY m.name,m.date) as 'PrevValue'
from myTable m
)  t where t.date='2022-01-03'
name city date value PrevValue
John NewYork 2022-01-03 20 10
Mark NewYork 2022-01-03 40 30

fiddle

More information (sqlshack.com/sql-lag-function-overview-and-examples). If you put an index on the fields you use "order by" (name, date etc.) it will work fast.

2
  • Thanks @omerix, my table is quite big (billions of rows) so I'm worried about the perofrmance because as I understand this query first calculates LAG for each row and then performs filtering am I correct? Can it somehow be optimized? Nov 9, 2022 at 18:39
  • 1
    More information. (sqlshack.com/sql-lag-function-overview-and-examples) If you put an index on the fields you use "order by", it will work fast. (name, date etc.)
    – omerix
    Nov 9, 2022 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.