sql - How to add the next most recent entry to my query, in some other way? -

i'm trying info next recent entry based on id , location , add query running. not second recent entry in table, previous entry based on id.

i'm not sure if possible or can't figure out how or direction should go.

here how previous programmer did it:

select -- blah blah blah        , (select top 1 cast(id varchar) + '~' + convert(varchar,  creation_date,   120)           header hdr2            hdr2.id < hdr.id                  , hdr2.location = hdr.location            order hdr2.id desc) "prev_info" header hdr  /*blah blah blah*/ 

i understand why wanted use single sub query , tried put 1 column, i'm trying optimize , in other,easier , more efficient, way.

i feel answer staring me in face, can't reach out , grasp it. if need explain myself more, please don't hesitate ask.

consider using outer apply:

select  -- blah blah blah ,       prev_info.*    header hdr  outer apply         (         select  top 1 id         ,       creation_date            header hdr2            hdr2.id < hdr.id                  , hdr2.location = hdr.location          order                 hdr2.id desc         ) prev_info