USQL query for SELECTING a range from a table

Question

Data is as

    index id
        1 112
        1 112
        2 109
        2 109
        3 125
        3 125
        4 199
        4 199
        5 100
        5 100

The id are not incremental but are sequential in nature take it as a GUID that's why i have assigned index for Range query

The user will give @startid @endid and i will get the rows for this range

Now first i get the index corresponding these id like

@indexes = SELECT DISTINCT index 
 FROM @table 
WHERE id IN (@startid, endid);

as a result i get (let's say for example if @startid = 2 and @endid = 4)

2
4

Now i know the Range will be BETWEEN 2 and 4 i.e. i want rows corresponding 2,3 and 4

@result= SELECT index AS index,
         id AS id 
         FROM @data
         WHERE 
         index BETWEEN (THE TWO ENTRIES FROM @indexes)

would have done this using Nested SELECT but USQL doesn't support it.

now is there a way to treat @indexes as a list and specify range or something???

Answer 1

BETWEEN is supported in U-SQL, it's just case-sensitive, eg

DECLARE CONST @startId int = 2;
DECLARE CONST @endId int = 4;


@input = SELECT *
     FROM (
        VALUES
        ( 1, 112 ),
        ( 1, 112 ),
        ( 2, 109 ),
        ( 2, 109 ),
        ( 3, 125 ),
        ( 3, 125 ),
        ( 4, 199 ),
        ( 4, 199 ),
        ( 5, 100 ),
        ( 5, 100 )
    ) AS x ( index, id );


@output =
    SELECT *
    FROM @input
    WHERE index BETWEEN @startId AND @endId;


OUTPUT @output TO "/output/output.csv"
USING Outputters.Csv(quoting:false);

My results:

Alternative approach:

    DECLARE CONST @startId int = 109;
    DECLARE CONST @endId int = 199;


    @input = SELECT *
         FROM (
            VALUES
            ( 1, 112 ),
            ( 1, 112 ),
            ( 2, 109 ),
            ( 2, 109 ),
            ( 3, 125 ),
            ( 3, 125 ),
            ( 4, 199 ),
            ( 4, 199 ),
            ( 5, 100 ),
            ( 5, 100 )
        ) AS x ( index, id );


@output =
    SELECT i. *
    FROM @input AS i
         CROSS JOIN
         (
         SELECT MIN(index) AS minIndex,
                MAX(index) AS maxIndex
         FROM @input AS i
         WHERE id IN ( @startId, @endId )
               ) AS x
    WHERE i.index BETWEEN x.minIndex AND x.maxIndex;    

    OUTPUT @output TO "/output/output.csv"
    USING Outputters.Csv(quoting:false);