cannot return a table grouped by variables in MS SQL server Management studio

Question

I created the following function:

CREATE FUNCTION categorize (
     @id VARCHAR(5),
     @var1_low INT,
     @var1_high INT,
     @var2_low DECIMAL(18,6),
     @var2_high DECIMAL(18,6)
    )
RETURNS TABLE
AS RETURN
    (SELECT
        year,
        id,
        COUNT(id) AS totalnum,
        SUM(score) AS var1_sum
     FROM
        database1
     WHERE
        studentid = @id
        AND Score >= @var1_low
        AND Score <= @var1_high
        AND time >= @var2_low
        AND time <= @var2_high
     GROUP BY
        year
     ORDER BY
        year
    )

Then it says

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

How can I solve this issue?

If you read the error message, you will see that your options are to specify `TOP` or `FOR XML`, or eliminate the `ORDER BY` clause. SQL Server is notorious for giving unhelpful error messages, but this isn't one of them. BTW, you're also going to need to add `id` to your `GROUP BY` clause. — Tab Alleman, Jan 05 '16 at 19:06

score 2 · Answer 1 · answered Jan 05 '16 at 18:54

2

Remove the ORDER BY clause in your function and write it in your SELECT clause like this

SELECT * FROM yourFunction(params) ORDER BY yourColumn

answered Jan 05 '16 at 18:54

chancrovsky

582
3
11

cannot return a table grouped by variables in MS SQL server Management studio

1 Answers1