Creating a scalar function that takes input, and ranks unit prices in descending order

user_12820690 0
Viewed 416

I am having a hard time developing a proper scalar function to work as intended. It needs to take an input of money type. I have a Products table, with a UnitPrice column that I am focused on. I need my function to sort products in descending order of unit prices, with the largest price having rank 1, next rank 2 etc. Same prices should rank the same and rank numbers must be sequentially and continuously assigned without skipping or jumping any numbers. That is, the ranks should look like 1, 2, 3, 4, 5,......, 61, and 62. Then, my function will find the rank of product(s) whose unit price equals to the input money value and return the rank number. If the input money value does not exist in the Products table, it returns -1. For example, because there are four products with a unit price of $18 and they are all ranked to be 40, RankOfGivenPrice(18) should return 40. Because no products have a unit price of $5.00, RankOfGivenPrice(5) returns -1.

My attempt: 

    go
create function RankOfGivenPrice
    (@unitprice money)
    returns money
begin
return
    (select TOP 1
    rank() over(order by UnitPrice desc) as RankNum
    from Products
    where UnitPrice = @unitprice)
end
go

This can compile, but it doesn't product the output I need. I call the function like select dbo.RankOfGivenPrice(18) but it returns 1.00. If I do not use TOP 1, I receive the "Subquery returned more than 1 value." error on function call. I assume I should be using the RANK() function but it is difficult with subquery.

sql-function window-functions tsql sql-server sql
3 Answers

You would need to rank in a subquery first, then filter on the parameter:

create function RankOfGivenPrice
    (@unitprice money)
    returns money
begin
return
    (
        select RankNum
        from (
            select UnitPrice, rank() over(order by UnitPrice desc) as RankNum
            from Products
        ) t
        where UnitPrice = @unitprice
    )
end

Most likely, you don't need TOP 1 in the outer query, unless more than one price matches @unitprice. In that event, you could also use an aggregate function like min() or max(), like:

return
    (
        select max(RankNum)
        from (
            select UnitPrice, rank() over(order by UnitPrice desc) as RankNum
            from Products
        ) t
        where UnitPrice = @unitprice
    )

If you want -1 for missing values:

return
    (
        select max(case when UnitPrice = @unitPrice then RankNum else -1 end)
        from (
            select UnitPrice, rank() over(order by UnitPrice desc) as RankNum
            from Products
        ) t
    )
user_106767160

Instead, use this logic:

 select count(*) + 1 as RankNum
 from Products
 where UnitPrice > @unitprice

You don't need the rank() function for this.

Here is a db<>fiddle.

user_11440350

You can use dense_rank() instead of rank You can read more about the function and how it works here

Using GMB's example the function would look like this.

GO
CREATE FUNCTION RankOfGivenPrice
    (@unitprice money)
    returns money
BEGIN
return
    (
        SELECT max(case when UnitPrice = @unitprice then Price_Rank else -1 end)
        FROM (
            SELECT UnitPrice, dense_rank() over (order by UnitPrice desc) as Price_Rank
            FROM Products
        )t

    )
END
GO
user_82344370
Related