CODEWITHSUNDEEP
sqlsql-server
PJW
PJW

Reputation: 5417

Retrieving Rows Above and Below Specified Rows in SQL Server

I have a large database in SQL Server of over a million lines of data, and 30+ columns (fields). I have run a routine to find 97 specific lines of particular interest to me from a single table. I have not included the various logical steps I took to isolate those 97 lines.

What I now need to do, is to sort my table into order by Date and Time, and retrieve not only the 97 lines I have identified (I have their IDs) but also the 5 data rows above and below each of the 97 in the sorted table.

Does anyone know how this can be achieved?

SELECT *
FROM table
WHERE ID IN (. . list of 97 IDs . . )
ORDER BY Date, Time
/* and get the 5 rows above and below each of the 97 specified lines */

I thought about using a difference between the specified IDs and the IDs other rows, but the Date, Time sort means the IDs are not in order anyway, so that field cannot be used to achieve my objective.

Upvotes: 0

Views: 276

Answers (2)

AK47
AK47

Reputation: 3807

Try this LOGIC, it differs from you table, but you can use same LOGIC & achive what you want.

;With CTE as 
(
select * 
 ,ROW_NUMBER() OVER (ORDER BY DateDt) AS RowNumber
 from mtT

), CTETop as
(
select top 5 *,'top' as section from CTE
order by RowNumber asc
),CTEBottom as 
(
select top 5 *,'bottom' as section from CTE
order by RowNumber desc
)
select * from CTETop

Union All

select * from CTEBottom

[DEMO]

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Use LAG and LEAD to get previous and following records and their ids:

with wanted_ids as (select list of 97 IDs)
select *
from
(
  select
    thetable.*,
    lag(id,1) over (order by thedate, thetime) as lag1,
    lag(id,2) over (order by thedate, thetime) as lag2,
    lag(id,3) over (order by thedate, thetime) as lag3,
    lag(id,4) over (order by thedate, thetime) as lag4,
    lag(id,5) over (order by thedate, thetime) as lag5,
    lead(id,1) over (order by thedate, thetime) as lead1,
    lead(id,2) over (order by thedate, thetime) as lead2,
    lead(id,3) over (order by thedate, thetime) as lead3,
    lead(id,4) over (order by thedate, thetime) as lead4,
    lead(id,5) over (order by thedate, thetime) as lead5
  from thetable
)
where id in wanted_ids
or lag1 in wanted_ids
or lag2 in wanted_ids
or lag3 in wanted_ids
or lag4 in wanted_ids
or lag5 in wanted_ids
or lead1 in wanted_ids
or lead2 in wanted_ids
or lead3 in wanted_ids
or lead4 in wanted_ids
or lead5 in wanted_ids
order by thedate, thetime;

You can do the same with a join. Only be aware not to get a record twice (when ranges overlap). Use distict to avoid this.

select distinct all_records.*
from
(
  select
    thetable.*,
    lag(id,1) over (order by thedate, thetime) as lag1,
    lag(id,2) over (order by thedate, thetime) as lag2,
    lag(id,3) over (order by thedate, thetime) as lag3,
    lag(id,4) over (order by thedate, thetime) as lag4,
    lag(id,5) over (order by thedate, thetime) as lag5,
    lead(id,1) over (order by thedate, thetime) as lead1,
    lead(id,2) over (order by thedate, thetime) as lead2,
    lead(id,3) over (order by thedate, thetime) as lead3,
    lead(id,4) over (order by thedate, thetime) as lead4,
    lead(id,5) over (order by thedate, thetime) as lead5
  from thetable
) all_records
inner join (select list of 97 IDs) wanted_ids 
on all_records.id = wanted_ids.id
or all_records.lag1 = wanted_ids.id
or all_records.lag2 = wanted_ids.id
or all_records.lag3 = wanted_ids.id
or all_records.lag4 = wanted_ids.id
or all_records.lag5 = wanted_ids.id
or all_records.lead1 = wanted_ids.id
or all_records.lead2 = wanted_ids.id
or all_records.lead3 = wanted_ids.id
or all_records.lead4 = wanted_ids.id
or all_records.lead5 = wanted_ids.id
order by thedate, thetime;

Upvotes: 1

Related Questions