2

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Chris
  • 41
  • 3

3 Answers3

0

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
    )
GMB
  • 216,147
  • 25
  • 84
  • 135
  • This is not quite it either. If I use either MIN or MAX, I receive 44 on dbo.RankOfGivenPrice(18) function call. I get NULL for non-existent values, which isn't what I want either. Should I add in a parameter that makes NULL values return as -1? Also I am not sure why it returns 44 and not 40. Seems to be adding on the amount of entries to the rank number. – Chris Apr 07 '20 at 18:11
  • @Chris: the ranking is OK, I guess that's about your data. I updated my answer with a solution to return -1 where there is no match. – GMB Apr 07 '20 at 21:12
0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This doesn't quite do the trick. If I call dbo.RankOfGivenPrice(18) it returns 44. I know there are 4 entries of 18, so I think it's adding on to the rank? If I try 5, it returns 76 for some reason, even though it does not exist in the column. – Chris Apr 07 '20 at 18:05
  • @Chris . . . This should do exactly the same thing that `rank() over (order by uniprice desc)` does. And, I added a db<>fiddle: it produces the same result when I test it. – Gordon Linoff Apr 07 '20 at 18:30
  • The data must be flawed then I guess. Not sure how to return a -1 for non-existent values either. – Chris Apr 07 '20 at 20:48
  • @Chris . . . The data is not "flawed", unless you are misinterpreting it. You can run the db<>fiddle. Whatever data you put into the `unitprice`, the function and `rank()` should return the same value. – Gordon Linoff Apr 07 '20 at 22:19
0

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
SnoopFrogg
  • 1