If I have a users table contains a column called Rate
and I need to get the maximum value from the rate column and name it position (position will be 1 for the max value only)
after that I need to get a specific row by Id .
and select the next 2 rows (next means the row's rate is bigger than the row I got) and previous 2 rows (rows' rate is smaller.
what is the query that can give me this result??

Ex:

and I need to get row has Id 2,the result should be:

CodePudding user response：

One option is literally just how it's written, unioned together:

select * from
(
  select top 2 * 
  from x 
  where 
    rate <= (select rate from x where id = 2) 
    and id <> 2
  order by rate desc
) lower

union all 

select * 
from x 
where id = 2

union all 

select * from
(
  select top 2 * 
  from x 
  where 
    rate >= (select rate from x where id = 2) 
    and id <> 2 
  order by rate
) higher

CodePudding user response：

A slightly more efficient version of @CauisJard's answer

• Get the rate of the row id = 2 first
• Then cross apply everything else
• Combine the unioned queries so id = 2 is retrieved with two others.

select
  data.*
from (
    select rate
    from x
    where id = 2
) StartPoint
cross apply
(
    select *
    from (
        select top (3) * 
        from x 
        where rate <= StartPoint.rate
        order by rate desc
    ) lower

    union all 

    select *
    from (
        select top (2) * 
        from x 
        where rate >= StartPoint.rate
          and id <> 2 
        order by rate
    ) higher
) data;