DolphinIron


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




Re: Row order

Kent Waldrop 2007 Mar


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.




Re: Row order

Arnie Rowland

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







Re: Row order

DolphinIron

I'm using SQL server 2000





Re: Row order

Arnie Rowland

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






Re: Row order

Umachandar Jayachandran - MS

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




Re: Row order

Jul

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





Re: Row order

Arnie Rowland

Something like this:

Code Snippet


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






Re: Row order

Jul

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.





Re: Row order

Arnie Rowland

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;

 





Re: Row order

Jul

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.





Re: Row order

Arnie Rowland

Please post your entire query.