5

I have to select 2 values that were less that the given id and greater than the given id. I have tried this query but is there any better way to do it

Fiddle Sql

Begin 

declare @rootValue int
declare @repID int

set @repID = 2
set @rootValue = (select Id from tblLookups where Id = @repID)

declare @rootMinusTwo int
declare @rootPlusTwo int

set @rootMinusTwo = (select count(*) from tblLookups where Id < @rootValue)
set @rootPlusTwo = (select count(*) from tblLookups where Id > @rootValue)

if @rootMinusTwo >= 2 and @rootPlusTwo >= 2
Begin
    select *  
    from tblLookups 
    where Id between @rootValue - 2 and @rootValue + 2
End
else if @rootMinusTwo < 2 and @rootPlusTwo >= 2
    select * 
    from tblLookups 
    where Id = @repID 
    union
    select * 
    from tblLookups 
    where Id < @rootValue 
    union
    select top (4 - @rootMinusTwo) * 
    from tblLookups 
    where Id > @rootValue
else if @rootMinusTwo >= 2 and @rootPlusTwo < 2
    select * 
    from tblLookups 
    where Id = @repID 
    union
    select * 
    from tblLookups 
    where Id > @rootValue 
    union
    select top(4 - @rootPlusTwo) * 
    from tblLookups 
    where Id < @rootValue
else if @rootMinusTwo < 2 and @rootPlusTwo < 2
    select * 
    from tblLookups 
    where Id = @repID 
    union
    select * 
    from tblLookups 
    where Id < @rootValue 
    union
    select * 
    from tblLookups 
    where Id > @rootValue
End

I forgot to add something at any point there should be 5 records if at all there are that many records satisfying the condition.

Example

The ID's are 1,2,3,4,5 if I supply 2 then also it should return 5

0

2 Answers 2

7

Quite convoluted but should work and return the 2 rows with values below the @repid, the row with @repid and the 2 rows with values above the @repid (assuming that id is a unique key).

If there are less than 2 values below or above, the query will get more from the other side. In total a maximum of 5 rows will be returned.

Note: the ORDER BY is needed. All three of them (not the 4th, that's only if you need the results in specific order):

with a as
  ( select top (4) 
        *, rnk = row_number() over (order by id desc) 
    from tblLookups
    where Id < @repID
    order by id desc
  ),
b as
  ( select top (5) 
        *, rnk = row_number() over (order by id) - 1
    from tblLookups
    where Id >= @repID
    order by id
  ),
c as 
  ( select top (5) *
    from
      ( select *
        from a
      union all
        select *
        from b
      ) x
    order by rnk 
  )
select *
from c
order by id ;

Tested at SQLfiddle.

6

The following is basically your algorithm but implemented as a single statement:

WITH ranked_and_counted AS (
  SELECT
    ID,
    r = ROW_NUMBER() OVER (ORDER BY ID ASC),
    c = COUNT(*) OVER ()
  FROM
    dbo.tblLookups
),
ranked_and_counted_and_r0 AS (
  SELECT
    *,
    r0 = MAX(CASE ID WHEN @ID THEN r END) OVER ()
  FROM
    ranked_and_counted
)
SELECT
  ID
FROM
  ranked_and_counted_and_r0
WHERE
  r >= CASE WHEN r0 > c - 2 THEN c - 4 ELSE r0 - 2 END
  AND
  r <= CASE WHEN r0 < 1 + 2 THEN 1 + 4 ELSE r0 + 2 END
;

The first CTE, ranked_and_counted calculates the row numbers and the total row count.

The second CTE, ranked_and_counted_and_r0 determines the row number for the row matching the specified @ID.

The main query uses the obtained values to get the required rows:

  • if the reference row number (r0) is one of the last two rows in the table, the current row's number (r) must be greater than or equal to the row count (c) minus four (i.e. must be one of the last five ones), otherwise it should be greater than or equal to the reference number minus two;

  • if the reference row is one of the first two rows, the current row number must be no greater than five, otherwise it should be less than or equal to the reference number plus two.

The query can be tested at SQL Fiddle. (The demo borrows ypercube's setup.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.