Problem on Virtual Column Sorting

  • WITH CandidateEntries AS (

    SELECT ROW_NUMBER() OVER (ORDER BY Salary) AS Row,

    [C].[Id],

    [C].[FirstName],

    [C].[MiddleName],

    [C].[LastName],

    [C].[PrimaryEmail],

    [C].[PrimaryPhone],

    dbo.GetSalary([C].[Id]) AS Salary

    FROM

    [dbo].[Candidate] AS [C]

    )

    SELECT

    *

    FROM

    CandidateEntries

    WHERE

    Row between 0 And 10

    -----------------------------------------------

    Error of this Query shows = Invalid column name 'Salary'

    -----------------------------------------------

    I need to sort on this salary column.

  • I'm afraid the compiler is right...

    Since this is a computed column, it cannot be used inside the same query.

    The options are:

    1. Include the salary function in the ORDER BY that generates the row number

    2. Take the row number computation out and use it in a wraping sql statement.:

    select [row number computation], * from (inner sql with function invocation)

    where record number between x and y

    Both solutions are not that pretty, since sorting on computed columns (and esp. if the invoke functions) is bad although common practice

  • Thanks a lot for this helpful reply

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply