0

I am working on a query to bring back a subset of data for our business analysts to review. I received one id to test my query against but there are so many use cases I need to pull N records for them to review. What I would like to do is return a sample size of records but guarantee the single lonely id is included.

Example I generate a list of numbers. I want to take the top 5 but the number 88 must be included in the result set.

DECLARE 
  @startnum INT=1,
  @endnum INT=100

;WITH gen AS (
    SELECT @startnum AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=@endnum
)
SELECT * FROM gen
option (maxrecursion 100)

num
-----------
1
2
3
4
88

Is this possible?

By the way the above is from Jayvee.

schwietertj
  • 185
  • 1
  • 9

2 Answers2

1

Could you do something like this:

DECLARE 
  @startnum INT=1,
  @endnum INT=100

;WITH gen AS (
    SELECT @startnum AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=@endnum
)
SELECT TOP 4 * FROM gen WHERE num <> 88
UNION ALL
SELECT * FROM gen WHERE num = 88
option (maxrecursion 100)
Chris Carr
  • 391
  • 2
  • 9
1

You can do this without union all by doing:

select top (5) g.*
from gen g
order by (case when num = 88 then 1 else 2 end), num;

Here is a SQL Fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I marked this one because it it only has a single statement. By virtue of limiting the query results with the inclusion of a single value I am good with the order by and case performance hit. – schwietertj Mar 11 '18 at 19:58