SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Paging Problem SQL 2005 using row_number and order by


Paging Problem SQL 2005 using row_number and order by

Author
Message
Nish-760535
Nish-760535
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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.
Ray Laubert
Ray Laubert
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1898 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
Florian Reischl
Florian Reischl
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15631 Visits: 3934
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
Nish-760535
Nish-760535
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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.
Nish-760535
Nish-760535
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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.
Florian Reischl
Florian Reischl
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15631 Visits: 3934
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
Nish-760535
Nish-760535
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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.
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61790 Visits: 9519
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."
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61790 Visits: 9519
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."
Nish-760535
Nish-760535
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search