Snowflake query for limit/top with condition-CodePudding

In Snowflake i was trying to limit the number of rows based on a condition. query is generated from the application.

Logic I was trying to achieve.

if the condition matches, then select all rows in the table
else limit to the given row number

let's consider a as row number count, if it is -1 then I want to retrieve all the rows.

SELECT * FROM "PUBLIC".TABLE1 LIMIT ( 
CASE WHEN a = -1
THEN
  -- no limit
ELSE 
 a

a will be resolved to number by application. Is this kind of operation is possible in Snowflake?

Above query is not working in Snowflake, just added it to specify the requirement

CodePudding user response：

I think you can achieve this by using the ROW_NUMBER and QUALIFY functions:

WITH sample_data AS (
  SELECT SEQ4() 1 AS val, 100 AS a
    FROM TABLE(GENERATOR(ROWCOUNT => 1000)) AS t
)
SELECT ROW_NUMBER() OVER(ORDER BY val) AS rn
  FROM sample_data
QUALIFY rn <= IFF(a = -1, rn, a);

Result: 100 rows

WITH sample_data AS (
  SELECT SEQ4() 1 AS val, -1 AS a
    FROM TABLE(GENERATOR(ROWCOUNT => 1000)) AS t
)
SELECT ROW_NUMBER() OVER(ORDER BY val) AS rn
  FROM sample_data
QUALIFY rn <= IFF(a = -1, rn, a);

Result: 1000 rows -- no limit, all rows

CodePudding user response：

Michael's answer is good, I flipped it to have a session variable just so there is a tiny bit of independence

SET A = 5;
/* session variable to "control" */

WITH sample_data AS (
  SELECT
    SEQ4() AS val
  FROM TABLE(GENERATOR(ROWCOUNT => 1000))
)
/* CTE for data */

SELECT *
    ,ROW_NUMBER() OVER(ORDER BY val) AS RN
FROM sample_data
QUALIFY RN <= IFF($a = -1, rn, $a);

VAL	RN
0	1
1	2
2	3
3	4
4	5

we can however move the ROW_NUMBER into the QUALIFY by negating the sign of ROW_NUMBER and some sign flipping, and thus instead of needing to know the current rows, or max rows rn we can compare to zero:

SELECT sd.*
FROM sample_data AS sd
QUALIFY ($a - ROW_NUMBER() OVER(ORDER BY sd.val)) * $a >= 0;

VAL
0
1
2
3
4

this is working because our row numbers are going from -1 -> -infinity but we take this from a limit (-1 or 5) and then multiplying that by limit which for negative value flips the sign to all positive, and for positive limit does nothing to the number of values that are positive

which can be seen in this working code:

SELECT *
   ,ROW_NUMBER() OVER(ORDER BY val) as rn
   ,(5 - rn) * 5
   ,(-1 - rn) * -1
FROM sample_data

which show how the math works out for 5 OR -1

VAL	RN	(5 - RN) * 5	(-1 - RN) * -1
0	1	20	2
1	2	15	3
2	3	10	4
3	4	5	5
4	5	0	6
5	6	-5	7
6	7	-10	8
7	8	-15	9
8	9	-20	10
9	10	-25	11

CodePudding user response：

You can use SQL Procedure (in public preview):

create or replace table test (a int);
insert into test values (1), (2), (3), (4), (5);
select * from test;

create or replace procedure dynamic_limit(limit int)
returns table(a int)
language sql
as
$$
    declare
        query string default 'SELECT * FROM TEST'; 
    begin
        IF (limit > 0) THEN
            query := :query || ' LIMIT ' || :limit;
        END IF;
    
        let res resultset default (execute immediate :query);
        return table(res);
    end;
$$;

call dynamic_limit(-1);
 --- 
| A |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
 --- 

call dynamic_limit(0);
 --- 
| A |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
 --- 

call dynamic_limit(1);
 --- 
| A |
|---|
| 1 |
 --- 

call dynamic_limit(4);
 --- 
| A |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
 ---