.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

Sql Server Query

Posted By: Naresh     Posted Date: March 29, 2010    Points:2   Category :Sql Server
hi,

I Have a table ,

Id Name location
----------------------------
001 xxx chennai
002 yyy banglore
001 xxx banglore
003 hhh delhi
002 yyy chandigar
001 xxx up


Now i want to update the table,
with a extra column called item_Id,



Id Name location item_id
--------------------------------
001 xxx chennai 1
002 yyy banglore 1
001 xxx banglore 2
003 hhh delhi 1
002 yyy chandigar 2
001 xxx UP 3

the item_id column must display , the increment of the number according to the repetition of the Id column.
I need to add the Item_Id number


thanks

Responses
Author: Suresh             
Posted Date: March 29, 2010     Points: 5   

Hello,
Kindly go through the below code. I hope ,it vil give soultion to ur query.


DECLARE @Table TABLE
(
ID varchar(50),
Name varchar(50),
location varchar(50)
)

INSERT INTO @Table
Select '001' ,'xxx' ,'chennai'
UNion ALl
Select '002', 'yyy', 'banglore'
UNion ALl
Select '001' ,'xxx' ,'banglore'
UNion ALl
Select '003' ,'hhh' ,'delhi'
UNion ALl
Select '002' ,'yyy' ,'chandigar'
UNion ALl
Select '001' ,'xxx' ,'up'


Select * from @Table

SELECT Id, Name, Location,Item_Id=
(
SELECT COUNT(*)
FROM @Table t2
WHERE t2.id = t1.id
AND t2.Location<= t1.Location
)
FROM @Table t1
ORDER BY id,Name, Location

All The Best

Author: Suresh             
Posted Date: April 24, 2010     Points: 5   

Hello,
Kindly try this query. its easy one compare to above one.

Select City,Team,row_number()
over(partition by City order by city ) rn from #teams

Author: Naresh             
Posted Date: May 21, 2010     Points: 5   

Hi ,

Suresh

Thank u for ur query,

I tried the query.

Yah it worked out




Thanks
Naresh

Thanks
Naresh


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend