CODEWITHSUNDEEP
sqlsql-server
Gabor
Gabor

Reputation: 1489

sql server access ONLY the previous record based on a selection

Given the following table

id  short_name  long_name   category    departure   rec_order
1548    AAA AAA AA  station 10:00   1
1548    BBB BBB BB  station 11:10   2
1548    CCC CCC CC  station 12:30   3
1548    DDD DDD DD  border  15:30   4

I want to access only last the row (station) before the border. Basically return only 

CCC CCC CC  station 12:30   3

Because the database is rather big and even this timetable is in the real life a complicated structure and we have in indexed view for putting together all the data (here is a simplification for the sake of example) I'm looking for a higher performance solution (possibly no cross join).

I've tried the selection with lag and/or lead, as well as offset fetch but those solutions were not working as soon as I put in the where clause where category = 'border' as it only selects the border row.

This query is working, but I'm looking for a more optimised solution:

select top 1 M1.short_name  M1.long_name
from V_TIMETABLE M1 WITH ( NOEXPAND )
join 
(
select top 1 rec_order, id
from V_TIMETABLE V WITH ( NOEXPAND ) 
where   V.id = 1548
and category = 'border'
order by rec_order desc
) M2 on M1.id = M2.id
where M1.rec_order < M2.rec_order
order by M1.rec_order desc

Upvotes: 0

Views: 40

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I just want to point out an alternative solution:

select t.*
from (select t.*,
             lead(category) over (partition by id order by departure) as next_category
      from t
     ) t
where next_category = 'border';

Important note: This will not return ids that have no border in them. It will also return multiple rows for ids that have border multiple times. These seem more in line with the question actually asked than always selecting one row -- and is consistent with the sample code provided in the question.

Upvotes: 2

TheGameiswar
TheGameiswar

Reputation: 28900

This works for given data

    ;with cte
    as
    (
    select 
    id, short_name , long_name,   category ,   departure  , rec_order,
    row_number() over (partition by id order by departure desc) as rownum
   from table where category<>'border'
    )
    select * from cte where rownum=1

Upvotes: 4

Related Questions