CODEWITHSUNDEEP
sql-serverfunction
zanhtet
zanhtet

Reputation: 2050

ORDER BY clause vs function of SQL Server

I want to use ORDER BY clause in functions of SQL Server. But I cannot use this, it not allow that. So, how can I use this?

CREATE FUNCTION PersonIDGet
    (

    )
RETURNS int
AS
BEGIN

    DECLARE @PersonID int;
        SET @PersonID = (SELECT PersonID FROM Person ORDER BY PersonID DESC);

        RETURN @PersonID

END

I got this error.

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.

Upvotes: 0

Views: 3468

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You need to have top 1 in your select statement. You are fetching more than one row so SQL Server have trouble figuring out what PersonID will be assigned to @PersonID.

CREATE FUNCTION PersonIDGet
    (

    )
RETURNS int
AS
BEGIN
    DECLARE @PersonID int;
    SET @PersonID = (SELECT TOP 1 PersonID FROM Person ORDER BY PersonID DESC);
    RETURN @PersonID
END

Upvotes: 3

Related Questions