CODEWITHSUNDEEP
t-sqlsql-server-2008rankinguser-defined-functions
RPM1984
RPM1984

Reputation: 73112

Help with RANK() Over Scalar Function - SQL Server 2008

I have the following Inline Table-Valued Function:

SELECT   Locations.LocationId, 
         dbo.Search_GetSuitability(@SearchPreferences,
            Score.FieldA, Score.FieldB, Score.FieldC) AS OverallSuitabilityScore,
        RANK() OVER (ORDER BY OverallSuitabilityScore) AS OverallSuitabilityRank

FROM        dbo.LocationsView Locations
INNER JOIN  dbo.LocationScores Score ON Locations.LocationId = Score.LocationId
WHERE       Locations.CityId = @LocationId

That RANK() line is giving me an error:

Invalid column name 'OverallSuitabilityScore'.

The function dbo.Search_GetSuitability is a scalar-function which returns a DECIMAL(8,5). I need to assign a rank to each row based on that value.

The only way i can get the above to work is to add the scalar function call in the ORDER BY part again - which is silly. I have about 5 of these scalar function calls and i need seperate RANK() values for each.

What can i do? Can i use a Common Table Expression (CTE) ?

Upvotes: 2

Views: 1878

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

An old school way of doing this is just to SUBQUERY the expression. The CTE here only moves the subquery to the top

SELECT   TOP(10) LocationId, 
        OverallSuitabilityScore,
        RANK() OVER (ORDER BY OverallSuitabilityScore) AS OverallSuitabilityRank
FROM
(
    SELECT
         Locations.LocationId, 
         dbo.Search_GetSuitability(@SearchPreferences,
             Score.FieldA, Score.FieldB, Score.FieldC) AS OverallSuitabilityScore
    FROM        dbo.LocationsView Locations
    INNER JOIN  dbo.LocationScores Score ON Locations.LocationId = Score.LocationId
    WHERE       Locations.CityId = @LocationId   
) X

Upvotes: 2

bobs
bobs

Reputation: 22184

Yep, you can't reference a column alias in the SELECT clause. The CTE sounds good though. Here's an example

WITH Score as
    (
    select Score.LocationId, Score.FieldA, Score.FieldB, Score.FieldC,
        dbo.Search_GetSuitability(@SearchPreferences,
            Score.FieldA, Score.FieldB, Score.FieldC) AS OverallSuitabilityScore
    from dbo.LocationScores
    )

SELECT   TOP(10) 
         Locations.LocationId, 
         Score.OverallSuitabilityScore,
        RANK() OVER (ORDER BY OverallSuitabilityScore) AS OverallSuitabilityRank

FROM        dbo.LocationsView Locations
INNER JOIN  Score ON Locations.LocationId = Score.LocationId
WHERE       Locations.CityId = @LocationId

Upvotes: 4

Related Questions