Post reply

How to ORDER BY calculated/computed field ?

  • August 3, 2009 at 2:18 am

    #133681

    Hi all,

    I'm having error when the calculated field is used as @sortExpression in ROW_NUMBER() OVER (calculatedField) for my pagination. The error : "Invalid column name 'calulatedField'.

    My query

    With SQLPaging

    As

    (

    SELECT pk, field_A + field_B as field_C

    ROW_NUMBER() OVER (ORDER BY field_C) as rowNum

    FROM myTable

    )

    select * from SQLPaging where rowNum > 1

    The following query is working, but doesn't work with complex field, eg. Using CASE.

    With SQLPaging

    As

    (

    SELECT pk, field_A + field_B as field_C

    ROW_NUMBER() OVER (ORDER BY field_A + field_B) as rowNum

    FROM myTable

    )

    select * from SQLPaging where rowNum > 1

    Is the a way to make it sortable by complex calculated field?

    Please advise, thanks.

  • August 3, 2009 at 1:34 pm

    #1033869

    You could use a derived table in the From clause:

    With SQLPaging

    As

    (

    SELECT pk, field_C

    ROW_NUMBER() OVER (ORDER BY field_C) as rowNum

    From (

    SELECT pk, field_A + field_B as field_C

    FROM myTable

    )

    select * from SQLPaging where rowNum > 1

  • August 3, 2009 at 2:54 pm

    #1033916

    I don't think it's necessary to use both a CTE and a derived table here. The following is untested, but should work.

    WITH SQLPaging AS (

    SELECT pk, field_A + field_B as field_C

    FROM myTable

    )

    SELECT pk, field_C

    ROW_NUMBER() OVER (ORDER BY field_C) as rowNum

    From SQLPaging

    WHERE rowNum > 1

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 3, 2009 at 3:31 pm

    #1033945

    After first fixing the missing comma after field_c in your code, you will get the following error:

    Msg 207, Level 16, State 1, Line 11

    Invalid column name 'rowNum'.

    You can't use the column alias for the column containing the row_number() function in the WHERE clause, so you do need to use a second CTE.

  • August 3, 2009 at 3:38 pm

    #1033953

    Yeah, I noticed that the alias would not be recognized after I posted. I really should have tested before posting. I still prefer a CTE to a derived table.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 3, 2009 at 7:32 pm

    #1034019

    Lynn Pettis (8/3/2009)

    After first fixing the missing comma after field_c in your code, you will get the following error:

    Msg 207, Level 16, State 1, Line 11

    Invalid column name 'rowNum'.

    You can't use the column alias for the column containing the row_number() function in the WHERE clause, so you do need to use a second CTE.

    drew.allen (8/3/2009)

    Yeah, I noticed that the alias would not be recognized after I posted. I really should have tested before posting. I still prefer a CTE to a derived table.

    Drew

    Hi, I'm new to CTE and have no idea on how to use 2 CTE together. Do you mind to post a sample?

    Thanks a lot. 🙂

  • August 3, 2009 at 7:56 pm

    #1034026

    Using the code from earlier in the thread, here is how it could be written using two CTE's.

    with myTableCTE as ( -- This CTE creates the column field_C that is used in the SQLPaging CTE

    select

    pk,

    field_A + field_B as field_C

    from

    myTable

    )

    , SQLPaging

    as

    (

    select

    pk,

    field_C,

    row_number() over (order y field_C) as rowNum

    from

    myTableCTE

    )

    select

    *

    from

    SQLPaging

    where

    rowNum > 1;

  • August 4, 2009 at 4:41 am

    #1034160

    It's working! Thanks a lot, Lynn Pettis and Allen 😀

  • August 4, 2009 at 9:37 am

    #1034354

    yaya1899 (8/4/2009)

    It's working! Thanks a lot, Lynn Pettis and Allen 😀

    Who is this "Allen" that you are referring to?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 4, 2009 at 10:27 am

    #1034400

    The following seems to work OK just using a single derived table.

    Create and load test data:

    -- Create test table

    create table #t

    (

    PKintnot null identity(1,1) primary key clustered,

    field_Abigintnot null,

    field_Bbigintnot null

    )

    --Load random test data

    insert into #t

    select top 15

    a.id,

    b.id

    from

    sys.sysobjects a,

    sys.sysobjects b

    order by

    newid()

    Query with derived table:

    select

    *

    from

    (

    select

    PK,

    field_a + field_b as field_c,

    row_number() over (order by field_a + field_b) as rownum

    from

    #t

    ) a

    where

    rownum > 1

    drop table #t

    Results:

    (15 row(s) affected)

    PK field_c rownum

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

    7 80 2

    8 115 3

    12 157 4

    11 159 5

    1 21575165 6

    10 21575193 7

    2 325576205 8

    15 325576249 9

    3 2009058206 10

    4 2041058365 11

    6 2057058424 12

    13 2073058465 13

    5 2302634277 14

    9 4050116500 15

    (14 row(s) affected)

  • August 4, 2009 at 7:34 pm

    #1034580

    drew.allen (8/4/2009)

    yaya1899 (8/4/2009)

    It's working! Thanks a lot, Lynn Pettis and Allen 😀

    Who is this "Allen" that you are referring to?

    Drew

    I'm referring to you, sorry for my mistake.:-)

    Michael Valentine Jones (8/4/2009)

    The following seems to work OK just using a single derived table.

    Create and load test data:

    ...

    Hi Micheal,

    Thanks for your sample.

    Here is my sample.

    -- * Sameple - ORDER BY Calulated/Computed Field for Pagination --

    DECLARE @Test TABLE (pk INT IDENTITY(1,1), field_A INT, field_B INT, field_C VARCHAR(12))

    INSERT INTO @Test VALUES (1,2,'1')

    INSERT INTO @Test VALUES (1,3,'1')

    INSERT INTO @Test VALUES (4,2,'1')

    INSERT INTO @Test VALUES (5,3,'1')

    INSERT INTO @Test VALUES (2,3,'1')

    INSERT INTO @Test VALUES (2,4,'1')

    ;

    WITH myTable AS

    (

    SELECT *, field_A + field_B AS field_D

    FROM @Test

    )

    , SQLPaging AS

    (

    SELECT *, ROW_NUMBER() OVER (ORDER BY field_D) AS rowNum FROM myTable

    )

    SELECT * FROM SQLPaging WHERE rowNum > 1

  • August 4, 2009 at 8:44 pm

    #1034594

    Please tell me you aren't using the * in the SELECT clause in your production code. You really should explicitly declare the columns you are returning in a select statement, even if you are returning all columns in the table.

  • August 5, 2009 at 3:36 am

    #1034668

    Lynn Pettis (8/4/2009)

    Please tell me you aren't using the * in the SELECT clause in your production code. You really should explicitly declare the columns you are returning in a select statement, even if you are returning all columns in the table.

    Thanks for reminding me. I only use SELECT * for myTable and SQLPaging, since all the required columns already decalred in the inner SELECT. Would that cause any issue?

    Thanks.

  • August 5, 2009 at 6:29 am

    #1034716

    You should still explicitly name the columns in the SELECT clause. Not that it may cause problems, it is just that is, for many of us, a bast practice.

  • August 6, 2009 at 7:12 pm

    #1035743

    Lynn Pettis (8/5/2009)

    You should still explicitly name the columns in the SELECT clause. Not that it may cause problems, it is just that is, for many of us, a bast practice.

    Hi Lynn Pettis,

    Thanks again for your advice. 🙂

Viewing 15 posts - 1 through 15 (of 15 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy