By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Help in writing SQL query

P: 21
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
Jun 25 '08 #1
Share this Question
Share on Google+
5 Replies


deepuv04
Expert 100+
P: 227
Hi,
use the following query:

Expand|Select|Wrap|Line Numbers
  1. SELECT Dense_Rank() over (PARTITION BY Field2 ORDER BY Field1) as ID,
  2.        Field1,Field2
  3. FROM Table_Name
  4.  
  5.  
Thanks
Jun 25 '08 #2

P: 21
What is this Dense_Rank() function in the query?
Jun 25 '08 #3

deepuv04
Expert 100+
P: 227
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
Jun 25 '08 #4

P: 21
OK! I think these are the new functions in SQL Server 2005.
But, I am using SQL Server 2000.
Jun 25 '08 #5

deepuv04
Expert 100+
P: 227
OK! I think these are the new functions in SQL Server 2005.
But, I am using SQL Server 2000.
hi,

try the following query

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT 
  3.     (select count(*) 
  4.     FROM Table_Name as B
  5.     WHERE B.Field1 <= A.Field1 and B.Field2 = a.Field2) as Id,
  6.     Field1,Field2
  7. FROM Table_Name as a
  8. ORDER BY 3,1
  9.  
  10. -- here the number 3,1 specifies order by 3 column , 1st column in the resultset
  11.  
thanks
Jun 25 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.