rowcount help

i'm trying to get total rows found by query that uses top clause...
for example:
select top 10 myTable.* from myTable where myTable.number > 200
let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.
is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().
tnx...> is there any way to get total row count, without affecting the TOP
> clause?
One method is with a subquery:
SELECT TOP 10
myTable.*,
(SELECT COUNT(*)
FROM myTable
WHERE myTable.number > 200) AS TotalRows
FROM myTable
WHERE myTable.number > 200
Hope this helps.
Dan Guzman
SQL Server MVP
"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128944.554703.296710@.38g2000cwa.googlegroups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...
>|||well, it helped...
but i'm worried about the execution time when using full-text search...
anyway, thanx for your help...|||You could select the entire set into a temp table or table variable.
Then, get the count of the temp table, then select the number you want.
e.g.
declare @.tAllRows as table(...)
insert into @.tAllRows
select ... from myTable where myTable.number>200
declare @.iTotalCount int
set @.iTotalCount = @.@.rowcount
select top 10 myTable.*, @.@.rowcount from @.tAllRows
While I usually try to avoid temp tables and table variables,
_sometimes_ they help a lot with performance. Benchmarking would be a
good idea, though, as my approach could be a disaster.
Also, are you paging based on an identity field? If so, you might not
get the results you expect when the numbers are not continuous.|||err...
select top 10 myTable.*, @.iTotalCount from @.tAllRows
my bad...

rowcount help

i'm trying to get total rows found by query that uses top clause...

for example:
select top 10 myTable.* from myTable where myTable.number > 200

let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.

is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().

tnx...I answered this question in microsoft.public.sqlserver.server.

It's bad etiquette to post the same question independently to multiple
groups because that causes unnecessary duplication of effort by persons
trying to help. If you have a question appropriate for multiple groups,
specify all groups in a single post so that all responses appear in all
groups.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128056.678229.59640@.i39g2000cwa.googlegro ups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...sql

Row order

Hi,

I want to show rows order (row number) in my query result set.
How can I do?

Example:

SELECT GetRowNumber(), Field1 FROM MY_TABLE WHERE ...

GetRowNumber(), Field1

1 Value1
2 Value2
3 Value4
.....

GetRowNumber() is a sp, udf, anyway

If you are using SQL Server 2005 consider the ROW_NUMBER() feature; look this up in books online. It is a useful feature that is new to SQL Server 2005.|||

Here is an example using the row_number() function:

USE Northwind
GO

SELECT
RowNumber = row_number() OVER ( ORDER BY LastName, FirstName ),
FirstName,
LastName
FROM Employees

|||

I'm using SQL server 2000

|||

These resources should give you the help you need.

Row Number (or Rank) from a SELECT Transact-SQL statement (includes Paging)
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx
http://www.projectdmx.com/tsql/ranking.aspx

|||What is the purpose of the row number? Why do you need it? Is it local to the results returned from the query? If so, why don't you generate it on the client-side? It is much easier to do and consumes less resources. You can use ROW_NUMBER or temporary table with identity column or sub-query to generate sequence numbers. But performance will not be that good with any of these methods so it depends on why you need to generate it in the first place?|||

Arnie,

This info is very helpful..however, i do have another question. How do i set the data types into varchar (6) for the row number? for example:

row firstname lastname

000001 richie rich

000002 john anderson

000003 will smith

000004 amber white

.

.

.

.

000010 william smith

instead of:

row firstname lastname

1 richie rich

2 john anderson

3 will smith

4 amber white

.

.

.

.

.

10 william smith

|||

Something like this:

Code Snippet

SELECT right(( '000000' + cast( row as varchar(6))), 6 )

|||

Thanks Arnie,

I have another issue to discuss. I have two different data to be entered into one sql table. I have done the first one which has let say, 2000 records. the second data needs to be entered into the table right after the first one.

let's say,

First data comes from a table named Service,

Second data comes from a table named File.

both different tables need to be mapped into one table with a primary key created with row order.

I have mapped Service with primary key 1 until 2000. Now, i want to continue from 2001 for File.

How do i generate a primary key that would continue from what i have left sequentially?

Thanks in advance,

Jul.

|||

One option would be to have an IDENTITY column for the new table, and set the start value as 2001.

Another option would be to use the same type of numbering query as posted above, and add 2000 to the values. Something like this:

Code Snippet

USE Northwind
GO

SELECT
c1.ContactName,
Rank = ( COUNT(*) + 2000 )
FROM Customers c1
JOIN Customers c2
ON c2.ContactName <= c1.ContactName
GROUP BY c1.ContactName
ORDER BY c1.ContactName;

|||

It gives me an error:

Column 'FBSourceTable..Case.CaseID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

|||Please post your entire query.

row number

how or what function can we use in sql server 2005 or 2000 to return the
corresponding row number of a record in select statement
for example
SELECT name, xxxxx as Number
FROM TableX
xxxxx - is the function or keyword that returns the corresponding row number
of the select statement.
the result set could be..
Name Number
John Doe 0
Jane Doe 1
0, 1 are the corresponding row number..
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787http://www.aspfaq.com/2427

> John Doe 0
> Jane Doe 1|||Jose,
In SQL Server 2000, you can derive row numbers using sub query like
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
SQL Server 2005 has an inbuilt ROW_NUMBER function.
SELECT ROW_NUMBER() OVER (ORDER BY job_desc ASC) as rownum, * FROM jobs
For more info see
http://toponewithties.blogspot.com/...es.blogspot.com
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||Using a identity field you obtain it. If you haven't I think exists a
internal rowid or something like that which provide us the row number.
see you,
"Jose G. de Jesus Jr MCP, MCDBA" wrote:

> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row numb
er
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||In SQL Server 2005, look up the ROW_NUMBER() function.
<Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||> If you haven't I think exists a
> internal rowid or something like that
There are internal row identifiers but these are not exposed to you, so you
can't use them in display.
Typically, the best way to present row numbers is to tack them on in the
presentation layer, since that's the only place where you *have to* loop
through and handle every single row anyway. Forcing the row numbers to be
generated in the database puts unnecessary strain there and turns a simple
query into either a subquery that is evaluated per row, or a mess with
pre-population into a temp table or table variable.|||>Using a identity field you obtain it
That behavior is not guaranteed.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30C1DECC-AF8C-4662-8EB3-3A1F0E3CBE26@.microsoft.com...
> Using a identity field you obtain it. If you haven't I think exists a
> internal rowid or something like that which provide us the row number.
> see you,
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>|||http://support.microsoft.com/defaul...b;EN-US;q186133
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||If one of the columns in query is unique, the following calculates a
sequence number for each row in a resultset:
SELECT
name,
(select count(*) from TableX as x where x.name > TableX.name) as Number
FROM
TableX
ORDER BY
name
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||I disagree. While it is true that using the IDENTITY function in a SELECT
INTO with an ORDER BY clause doesn't guarantee that the order of the
IDENTITY values match the order specified in the ORDER BY clause, using an
INSERT...SELECT...ORDER BY to insert into a temporary table or table
variable with an IDENTITY column will always work correctly. See KB273586.
An obvious improvement, however, is the ROW_NUMBER() function in SQL Server
2005, which eliminates the need for the self-join or the intermediate temp
table or table variable.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:OEtZ0dKqFHA.3192@.TK2MSFTNGP10.phx.gbl...
> That behavior is not guaranteed.
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:30C1DECC-AF8C-4662-8EB3-3A1F0E3CBE26@.microsoft.com...
the
>