Select info from previous row in SQL Server

Question

My table mixes (the interesting cols):

id	article	ordenr	start_date
1	a	10001	2024-01-01
2	b	10002	2024-01-03
3	c	10004	2024-01-02
4	a	10003

How do I get the previous article of ordernr based on start_date. I have tried LAG and that works fine until I include the current ordernr (say 10004) then it just return NULL.

This works, I get a list with the correct previous article and ordernr.

SELECT TOP (1) 
    article, ordernr,
    LAG(ordernr) OVER (ORDER BY start_date) AS prev_ordernr,
    LAG(article) OVER (ORDER BY start_date) AS prev_article
FROM 
    mixes
ORDER BY 
    start_date DESC

But then I want a specific row, and add: where ordernr = '10004', it just returns NULL on in the prev_article and prev_ordernr.

How do I solve this?

Can you show us the code that "isn't working"? That attempt works exactly as expected. What are you expected results here exactly? — Thom A
– Thom A ♦, Commented Feb 11, 2024 at 10:44
Sounds like you might need to put LAG into a subquery and then filter afterwards. But if it's only one row then an APPLY (SELECT TOP 1 might be better. — Charlieface
– Charlieface, Commented Feb 11, 2024 at 10:47

Sterner · Accepted Answer · 2024-02-11 10:50:57Z

1

You could outer join the previous values.

DATA USED

IF(OBJECT_ID('tempdb..#tmp_mixes') is not null)
    DROP TABLE #tmp_mixes

CREATE TABLE #tmp_mixes (id int, article char(1), ordenr int, start_date datetime)

INSERT INTO #tmp_mixes
VALUES
(1, 'a', 10001, '2024-01-01'),
(2, 'b', 10002, '2024-01-03'),
(3, 'c', 10004, '2024-01-02'),
(4, 'a', 10003, null)

Something like:

SELECT
    m.article,
    m.ordenr,
    m.start_date,
    prev.ordenr AS prev_ordernr,
    prev.article AS prev_article
FROM #tmp_mixes m
OUTER APPLY (
    SELECT TOP 1
        ordenr, article
    FROM #tmp_mixes prev 
    WHERE prev.start_date < m.start_date
    order by m.start_date desc
    ) prev
WHERE m.ordenr = 10004
order by m.start_date desc

Results

article	ordenr	start_date	prev_ordernr	prev_article
c	10004	2024-01-02 00:00:00.000	10001	a

answered Feb 11, 2024 at 10:50

Sterner

4523 silver badges7 bronze badges

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

Comments

Marcello Miorelli · Accepted Answer · 2024-02-11 14:32:40Z

I will use the create and populate table done by Sterner, with thanks

IF(OBJECT_ID('tempdb..#tmp_mixes') is not null)
    DROP TABLE #tmp_mixes

CREATE TABLE #tmp_mixes (id int, article char(1), 
              ordernr int, start_date datetime)

INSERT INTO #tmp_mixes
VALUES
(1, 'a', 10001, '2024-01-01'),
(2, 'b', 10002, '2024-01-03'),
(3, 'c', 10004, '2024-01-02'),
(4, 'a', 10003, null)

select * from #tmp_mixes

and I will use LAG and LEAD as you originally did:

;WITH cte AS (
    SELECT article , ordernr,start_date, id,
           LAG(ordernr ) OVER (ORDER BY start_date )  lag_value,
           LEAD(ordernr ) OVER (ORDER BY start_date ) lead_value
    FROM #tmp_mixes
)
SELECT id, start_date, ordernr, 
lag_value AS prev_ordernr,
lead_value AS next_ordernr
FROM cte
ORDER BY  2

that gives me the following:

which I believe it is what you wanted.

However, it just doesn't stop there.The lead and lag

functions have now (since sql 2022 I think) parameters so that you can decided how you go on about nulls

LEAD ( scalar_expression [ , offset ] , [ default ] ) [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause )

Another way you could try is using LAST_VALUE

Collectives™ on Stack Overflow

Select info from previous row in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related