CODEWITHSUNDEEP
sqlselect
Satya Ashok Kumar
Satya Ashok Kumar

Reputation: 95

SELECT TOP <negative number>

I have encountered a unique problem. Does anybody know if the SELECT TOP command of SQL can take any other parameters as input other than integers. Is there any special case where -1 is used as a parameter for some special functions. (Eg. SELECT TOP -1 from...).

Upvotes: 0

Views: 2190

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32180

Check the TOP documentation for the syntax of the expression. You can specify an absolute number of rows or a percentage. You can include ties, optionally.

It does not allow negative numbers. Executing:

SELECT TOP(-1) *
FROM Table
ORDER BY Id

Generates the error: A TOP N value may not be negative.

If you want the bottom row, you must modify the ORDER BY clause:

SELECT TOP(1) *
FROM Table
ORDER BY Id DESC

It has some other odd behavior. You can often omit the parentheses, but you may lose functionality in some cases. For example, including them means you can specify a parameter, such as:

DECLARE @rows INT = 10;

SELECT TOP(@rows) *
FROM Table
ORDER BY Id DESC;

But this generates a syntax error:

DECLARE @rows INT = 10;

SELECT TOP @rows *
FROM Table
ORDER BY Id DESC;

Finally, remember that TOP is non-deterministic (a four letter word in RDBMSs) without ORDER BY.

Upvotes: 2

Related Questions