how to COUNT in WITH? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to COUNT in WITH?

Author

Topic

MacJK
Starting Member

24 Posts

Posted - 2010-09-12 : 04:26:21

Hello, i wondering how the the the total count from a paaging Query in SQL?

My Code:



DECLARE @Page int
DECLARE @PageSize int
DECLARE @ResultCount int

SET @Page = 1
SET @PageSize = 25

BEGIN
WITH OrderPage (Number, Factory, OrderNumber, CustomerNumber) AS
	(
		SELECT ROW_NUMBER() OVER (ORDER BY OrderNumber)
	) AS Number, Factory, OrderNumber, CustomerNumber
		FROM [dbo].[Table_SalesOrder]

	)
SELECT Number, Factory, OrderNumber, CustomerNumber FROM OrderPage WHERE Number BETWEEN
((@PageSize * @Page) - ( @PageSize - 1)) AND (@PageSize * @Page)
END

My Question how to get the total count of the SELECT ROW_Number?

Is there a way to get this by @@rowcount?
Or did i need to make a second (same) Count(*) query.

Thank you all for sharing ideas an Tips!

br
MacJK

br
Jaroslaw

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-12 : 05:06:37

you can add

select @@rowcount

at the end of your statements and you will receive how many rows were returned by your query in separate result window.

i also don't see any use of With in your case nor another select to return Row_number() statement.

try this:


SELECT
X.*
(
SELECT 
	ROW_NUMBER() OVER (ORDER BY OrderNumber) AS Number
	,Factory
	,OrderNumber
	,CustomerNumber
FROM [dbo].[Table_SalesOrder]
) as X
WHERE x.Number BETWEEN
((@PageSize * @Page) - ( @PageSize - 1)) AND (@PageSize * @Page)

SELECT @@ROWCOUNT

MacJK
Starting Member

24 Posts

Posted - 2010-09-12 : 05:56:13

Hello, ok this deliver the total count of the SELECT mostly 25 becouse the page size is 25 but sometimes also less.

But what i want to get is the total from the X Table what sould be much more the 25 my be 150 what mean i have 6 pages a'25 .

br
Jaroslaw

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-12 : 13:48:53

Hello, i wondering how the the the total count from a paaging Query in SQL?

I think you try to do paging for web. If so, that will need a litle help from frontend. Send @totalRow to the SQL from frontend. If it is null (the first hit), then run an extra query to get totalRow. Else run your query like normal. Many big forums on internet using this way. Also for better performance, you should use javascript to generate the page, not server side.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-12 : 14:39:10

MacJK,if you want to have pages shown for a web page, you ought to solve this as namman said.

MacJK
Starting Member

24 Posts

Posted - 2010-09-13 : 06:09:36

Hello namman and slimt_slimt,

Yes i try to get a paging for Web frontend. I understand the method you suggest I think myself about this a little but want be shore there is no better way to save this additional query.

But with your way I can move forward. Many thanks all for the Information.

br
Jaroslaw

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-13 : 06:22:22

quote:

Originally posted by MacJK

DECLARE @Page int
DECLARE @PageSize int
DECLARE @ResultCount int

SET @Page = 1
SET @PageSize = 25

BEGIN
WITH OrderPage (Number, Factory, OrderNumber, CustomerNumber) AS
	(
		SELECT ROW_NUMBER() OVER (ORDER BY OrderNumber)
	) AS Number, Factory, OrderNumber, CustomerNumber
		FROM [dbo].[Table_SalesOrder]

	)
SELECT Number, Factory, OrderNumber, CustomerNumber FROM OrderPage
, (SELECT COUNT(*) FROM [dbo].[Table_SalesOrder]) AS TotalOrders
WHERE Number BETWEEN
((@PageSize * @Page) - ( @PageSize - 1)) AND (@PageSize * @Page)
END

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources