Help in writing SQL query

[cathy25]

Hi,
I have a table like this with two fields Field1,Field2.

Field 1 Field 2

00345 Hello world
00456 Hello world
09934 Hello world
78900 New filed
12345 New filed
24423 New filed
98778 How are you
12355 How are you
344545 How are you
22223 How are you

I have to add an extra field called ID which should generate a sequential number depending on Field2.
The out put should be like this:

ID Field 1 Field2
1 00345 Hello world
2 00456 Hello world
3 09934 Hello world

1 78900 New filed
2 12345 New filed
3 24423 New filed

1 98778 How are you
2 12355 How are you
3 344545 How are you
4 22223 How are you

I need to create a count and increase it depending on Field2. whenever field 2 changes I need to reset the count.

Could any body help me in writing a stored procedure for this.

TIA

[deepuv04]

Hi,
use the following query:

SELECT Dense_Rank() over (PARTITION BY Field2 ORDER BY Field1) as ID,
       Field1,Field2
FROM Table_Name
 
 

Thanks

[cathy25]

What is this Dense_Rank() function in the query?

[deepuv04]

What is this Dense_Rank() function in the query?

Hi,

There are four different functions to assign a rank value for reach row.
Rank(),
Row_Number(),
Ntile(),
Dense_Rank()

for more details visit the following link

http://technet.microsoft.com/en-us/library/ms189798.aspx

Thanks

[cathy25]

OK! I think these are the new functions in SQL Server 2005.
But, I am using SQL Server 2000.

[deepuv04]

OK! I think these are the new functions in SQL Server 2005.
But, I am using SQL Server 2000.

hi,

try the following query

 
SELECT 
    (select count(*) 
    FROM Table_Name as B
    WHERE B.Field1 <= A.Field1 and B.Field2 = a.Field2) as Id,
    Field1,Field2
FROM Table_Name as a
ORDER BY 3,1
 
-- here the number 3,1 specifies order by 3 column , 1st column in the resultset
 

thanks