Paging Problem SQL 2005 using row_number and order by

  • Hi Guys,

    I'm having a strange problem, with paging on SQL server 2005. Following is the details.

    Company - Table

    companyid

    Companyname

    Job -Table

    Jobid

    Companyid

    Job Desc

    With Cust AS

    ( SELECT companyid, CompanyName,

    (Select count(*) from job where companyid = company.companyid) as jobcount,

    ROW_NUMBER() OVER (order by jobcount) as RowNumber

    FROM company

    )

    select *

    from Cust

    Where RowNumber >= 0 and RowNumber <= 10 It's not allowing me to use JOBCOUNT column inn order by clause, i've to allow sorting on the jobcount column as users might want to know companies with most jobs or least jobs. i have 1.5 mill companies and each can any number of jobs. Thanks,
    Nish.

  • Create a view with out the order by. Then select from the view and order by the results. That should work.

    Raymond Laubert
    Exceptional DBA of 2009 Finalist
    MCT, MCDBA, MCITP:SQL 2005 Admin,
    MCSE, OCP:10g

  • Hi

    Seems that the JobCount column is not yet available for the ROW_NUMBER order within the SELECT clause.What about a second CTE?

    DECLARE @Company TABLE (CompanyId INT, CompanyName VARCHAR(30))

    DECLARE @Job TABLE (JobId INT, CompanyId INT, Name VARCHAR(100))

    INSERT INTO @Company

    SELECT 1, 'Acme'

    UNION ALL SELECT 2, 'Umbrella Corp.'

    INSERT INTO @Job

    SELECT 1, 1, 'John Doe'

    UNION ALL SELECT 2, 1, 'Jane Doe'

    UNION ALL SELECT 3, 2, 'Alice'

    ; WITH

    JobCount AS

    (

    SELECT

    CompanyId,

    COUNT(*) JobCount

    FROM @Job

    GROUP BY CompanyId

    ),

    Cust AS

    (

    SELECT

    c.CompanyId,

    j.JobCount,

    ROW_NUMBER() OVER (ORDER BY j.JobCount) AS RowNumber

    FROM @Company c

    JOIN JobCount j ON c.CompanyId = j.CompanyId

    )

    SELECT

    *

    FROM Cust

    Greets

    Flo

  • this solves the problem, but it seems there's another one.

    the order by column and page numbers come from UI(ASP.NET), so it'll only send column name i.e "JobCount" , but in the query it needs to be written like "JobCount.JobCount" or "Company.CompanyID" , my UI does not understand this and there are 6 different columns which comes from 3 different tables.

    Is there any workaround?

    Thanks In Advance,

    Nish.

  • Ray Laubert (5/13/2009)


    Create a view with out the order by. Then select from the view and order by the results. That should work.

    I've heard that views are not that great performance wise. and not a good candidate unless they will b e used by more than one cases.

    will there be any performance hit?

    Tahnks,

    Nish.

  • Nish (5/13/2009)


    this solves the problem, but it seems there's another one.

    the order by column and page numbers come from UI(ASP.NET), so it'll only send column name i.e "JobCount" , but in the query it needs to be written like "JobCount.JobCount" or "Company.CompanyID" , my UI does not understand this and there are 6 different columns which comes from 3 different tables.

    Is there any workaround?

    Thanks In Advance,

    Nish.

    Hi Nish

    I have no idea what you mean with "JobCount.JobCount" for your GUI. If you use a DataTable (or a DataReader) you get only "JobCount" as result column, don't you?

    Greets

    Flo

  • Florian Reischl (5/13/2009)


    Nish (5/13/2009)


    this solves the problem, but it seems there's another one.

    the order by column and page numbers come from UI(ASP.NET), so it'll only send column name i.e "JobCount" , but in the query it needs to be written like "JobCount.JobCount" or "Company.CompanyID" , my UI does not understand this and there are 6 different columns which comes from 3 different tables.

    Is there any workaround?

    Thanks In Advance,

    Nish.

    Hi Nish

    I have no idea what you mean with "JobCount.JobCount" for your GUI. If you use a DataTable (or a DataReader) you get only "JobCount" as result column, don't you?

    Greets

    Flo

    Sorry for not explaining properly,

    To sort on Jobcount , i have to write "(ORDER BY j.JobCount)" , but as you said datatable does not know the table prifix, same with sort on company i'll have to pass "(ORDER BY C.CompanyId" )" , so i cant know the table alias on the runtime. is there any workaround, i guess not. but anyother method where i dont have to pass the table alias.

    Thanks,

    Nish.

  • Nish (5/13/2009)


    Sorry for not explaining properly,

    To sort on Jobcount , i have to write "(ORDER BY j.JobCount)" , but as you said datatable does not know the table prifix, same with sort on company i'll have to pass "(ORDER BY C.CompanyId" )" , so i cant know the table alias on the runtime. is there any workaround, i guess not. but anyother method where i dont have to pass the table alias.

    No you don't. ORDER BY columns in fact are supposed to use thier output names and not their input names. So using Flo's example, this works fine:

    ; WITH JobCount AS (

    SELECT

    CompanyId,

    COUNT(*) JobCount

    FROM @Job

    GROUP BY CompanyId

    ), Cust AS (

    SELECT

    c.CompanyId,

    j.JobCount,

    ROW_NUMBER() OVER (ORDER BY j.JobCount) AS RowNumber

    FROM @Company c

    JOIN JobCount j ON c.CompanyId = j.CompanyId

    )

    SELECT

    *

    FROM Cust

    -->

    ORDER BY JobCount--this works

    -->

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Nish (5/13/2009)


    Ray Laubert (5/13/2009)


    Create a view with out the order by. Then select from the view and order by the results. That should work.

    I've heard that views are not that great performance wise. and not a good candidate unless they will b e used by more than one cases.

    will there be any performance hit?

    No this is not true. Views and CTEs are arguably the best performing objects even theoretically possible because they are just table expressions. There's no inherent overhead with a View or CTE.

    However, like anything else, using them improperly can cause performance problems. In particular, using Views (or worse nested Views), that contain extra stuff that you do not need for a query (extra tables, function calls, calculations, etc.) CAN cause performance problems.

    But it has nothing to do with Views, per se, it's just that you are writing an inefficient query that is doing all kinds of work that you do not need.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (5/13/2009)


    Nish (5/13/2009)


    Sorry for not explaining properly,

    To sort on Jobcount , i have to write "(ORDER BY j.JobCount)" , but as you said datatable does not know the table prifix, same with sort on company i'll have to pass "(ORDER BY C.CompanyId" )" , so i cant know the table alias on the runtime. is there any workaround, i guess not. but anyother method where i dont have to pass the table alias.

    No you don't. ORDER BY columns in fact are supposed to use thier output names and not their input names. So using Flo's example, this works fine:

    ; WITH JobCount AS (

    SELECT

    CompanyId,

    COUNT(*) JobCount

    FROM @Job

    GROUP BY CompanyId

    ), Cust AS (

    SELECT

    c.CompanyId,

    j.JobCount,

    ROW_NUMBER() OVER (ORDER BY j.JobCount) AS RowNumber

    FROM @Company c

    JOIN JobCount j ON c.CompanyId = j.CompanyId

    )

    SELECT

    *

    FROM Cust

    -->

    ORDER BY JobCount--this works

    -->

    Thanks for quick reply,

    My UI will send only column names, i.e "CompanyName Desc" , to order correctly by company name i'll have to change

    " over (ORDER BY j.JobCount) " to " over (order by CompanyName) " , if i want to get correct results, just changing the outside order by wont work. as i'm also doing paging " where Rownumber > 10 and rownumber <=100"

    Thanks,

    Nish.

  • Views are treated no different than adhoc queries by SQL, unless you are doing Indexed Views. So the Execution plan for both adhoc and view will be the same.

    Raymond Laubert
    Exceptional DBA of 2009 Finalist
    MCT, MCDBA, MCITP:SQL 2005 Admin,
    MCSE, OCP:10g

Viewing 11 posts - 1 through 10 (of 10 total)

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