Count Row in inner SELECT - 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)

Count Row in inner SELECT

Author

Topic

InterNeo
Starting Member

6 Posts

Posted - 2011-09-12 : 04:14:03

[code]WITH ans AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY contractorId) Row,
p.contractorId,
p.name,
p.city,
contractor.f_getAttr(contractorId) as attributes,
p.contractorStatus,
a.agentId as guardianId,
a.name+' '+a.surname as guardianFullName
FROM contractor.contractor p
LEFT JOIN contractor.relationAgent ra
ON p.contractorId = ra.contractorRef
LEFT JOIN agent.agent a
ON a.agentId = ra.agentRef
LEFT JOIN contractor.relationContact rc
ON p.contractorId = rc.contractorRef
LEFT JOIN contractor.contact c
ON c.contactId = rc.contactRef
WHERE rc.relationSymbol='contractorAdmin' AND ra.relationSymbol='contractorGuardian'
and a.agentId = isnull(@G,a.agentId) AND p.contractorStatus=isnull(@stat,p.contractorStatus)
-- eg 4 rows - i need this info
)
SELECT
*
FROM ans
WHERE Row BETWEEN (@page*@limit-@limit) AND (@page*@limit)

-- eg. SELECT * FROM contractor.contact has 8 rows
[/code]

Hi,
How can I assign to variable row count from select inside with-as?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 04:19:06

do you mean this? rowCnt will have total rowcount value

WITH ans AS
(
SELECT COUNT(1) OVER () AS RowCnt,
	ROW_NUMBER() OVER(ORDER BY contractorId) Row,
	p.contractorId,
	p.name,
	p.city,
	contractor.f_getAttr(contractorId) as attributes,
	p.contractorStatus,
	a.agentId as guardianId,
	a.name+' '+a.surname as guardianFullName
FROM contractor.contractor p
LEFT JOIN contractor.relationAgent ra
ON p.contractorId = ra.contractorRef
LEFT JOIN agent.agent a
ON a.agentId = ra.agentRef
LEFT JOIN contractor.relationContact rc
ON p.contractorId = rc.contractorRef
LEFT JOIN contractor.contact c
ON c.contactId = rc.contactRef
WHERE rc.relationSymbol='contractorAdmin' AND ra.relationSymbol='contractorGuardian'
 and a.agentId = isnull(@G,a.agentId) AND p.contractorStatus=isnull(@stat,p.contractorStatus)
 -- eg 4 rows - i need this info
)
SELECT 
	*
  FROM ans
 WHERE Row BETWEEN (@page*@limit-@limit) AND (@page*@limit)

-- eg. SELECT * FROM contractor.contact has 8 rows

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

InterNeo
Starting Member

6 Posts

Posted - 2011-09-12 : 04:23:43

Yes, but here RowCnt is a column with value in each row. I need this value in variable - is it possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 04:26:36

inside cte its not possible. you've use separate select for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

InterNeo
Starting Member

6 Posts

Posted - 2011-09-12 : 04:32:01

Ok...

So, when I have:

SELECT 
	*
  FROM ans
 WHERE Row BETWEEN (@page*@limit-@limit) AND (@page*@limit)

How can I send this select, AND AFTER it, next select (multiple SQL ResultSets) like:

SELECT RowCnt FROM ?? GROUP BY RowCnt

As before, I used "WITH ans AS"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 04:34:35

[code]
SELECT
*
FROM ans
WHERE Row BETWEEN (@page*@limit-@limit) AND (@page*@limit)

SELECT TOP 1 RowCnt FROM ...
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

InterNeo
Starting Member

6 Posts

Posted - 2011-09-12 : 04:37:22

I cant do that, bc on second select i get:

Invalid object name 'ans'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 05:27:16

why you need to select from cte again?

whats it that you want rowcount for?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

InterNeo
Starting Member

6 Posts

Posted - 2011-09-12 : 08:45:27

Not for me - its for .net controller.
Its need 2 datasets. First with data, second with another data like size of all records

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 09:15:12

if you want to use it again, why not use a temporary table instead of cte?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

InterNeo
Starting Member

6 Posts

Posted - 2011-09-12 : 09:49:19

This is always a way out... Im just wondering how to do it in simplest way

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 10:33:50

its still the simplest way. didnt understand whats the problem in using a temporary table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources