Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Paging Problem SQL 2005 using row_number and order by Expand / Collapse
Author
Message
Posted Wednesday, May 13, 2009 10:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 3:40 PM
Points: 5, Visits: 7
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.

Post #716214
Posted Wednesday, May 13, 2009 1:37 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 8:35 AM
Points: 1,608, Visits: 198
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
Post #716377
Posted Wednesday, May 13, 2009 1:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #716385
Posted Wednesday, May 13, 2009 2:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 3:40 PM
Points: 5, Visits: 7
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.
Post #716418
Posted Wednesday, May 13, 2009 2:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 3:40 PM
Points: 5, Visits: 7
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.
Post #716421
Posted Wednesday, May 13, 2009 2:37 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #716433
Posted Wednesday, May 13, 2009 2:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 3:40 PM
Points: 5, Visits: 7
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.
Post #716450
Posted Wednesday, May 13, 2009 3:21 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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
-->



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #716487
Posted Wednesday, May 13, 2009 3:27 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #716492
Posted Wednesday, May 13, 2009 3:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 3:40 PM
Points: 5, Visits: 7
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.
Post #716512
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse