T-SQL Problem with SELECT TOP (case [...])

Question

I have a query like this:

(as you can see, I would like to get 50% of the total number of lines or the first 100 lines, etc.)

//@AllRowsSelectType is INT

 SELECT TOP (
    case @AllRowsSelectType
        when 1 then 100 PERCENT 
        when 2 then 50 PERCENT
        when 3 then 25 PERCENT
        when 4 then 33 PERCENT
        when 5 then 50
        when 6 then 100
        when 7 then 200 
    end
            ) ROW_NUMBER() OVER(ORDER BY [id]) AS row_num, a,b,c etc

why I was wrong: "The syntax is incorrect next to the keyword" PERCENT ". in the line" when 1 [...] "

Answer 1

Syntax TOP :

TOP (expression) [PERCENT]
[ WITH TIES ]

The reserved keyword PERCENT cannot be included in the expression. Instead, you can run two different queries: one for when you want PERCENT, and the other when you do not.

, , UNION ALL :

SELECT TOP (
    CASE @AllRowsSelectType
        WHEN 1 THEN 100
        WHEN 2 THEN 50
        WHEN 3 THEN 25
        WHEN 4 THEN 33 
        ELSE 0
    END) PERCENT
    ROW_NUMBER() OVER(ORDER BY [id]) AS row_num, a, b, c, ...
UNION ALL
SELECT TOP (
    CASE @AllRowsSelectType
        WHEN 5 THEN 50
        WHEN 6 THEN 100
        WHEN 7 THEN 200
        ELSE 0
    END)
    ROW_NUMBER() OVER(ORDER BY [id]) AS row_num, a, b, c, ...

Answer 2

. .

DECLARE @ROW_LIMT int

IF @AllRowsSelectType < 5
    SELECT @ROW_LIMIT = COUNT(*)/@AllRowsSelectType FROM myTable -- 100%, 50%, 33%, 25%
ELSE
    SELECT @ROW_LIMIT = 50 * POWER(2, @AllRowsSelectType - 5) -- 50, 100, 200...

WITH OrderedMyTable
(
  select *, ROW_NUMBER() OVER (ORDER BY id) as rowNum
  FROM myTable
)
SELECT * FROM OrderedMyTable
WHERE rowNum <= @ROW_LIMIT

Answer 3

You can do:

select top (CASE @FilterType WHEN 2 THEN 50 WHEN 3 THEN 25 WHEN 4 THEN 33 ELSE 100 END) percent * from
(select top (CASE @FilterType WHEN 5 THEN 50 WHEN 6 THEN 100 WHEN 7 THEN 200 ELSE 2147483647 END) * from
<your query here>
) t
) t

What could be easier to read.