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.