SQL Consecutive Numbers

[cotton31]

I have a database which looks like the following:

Category Item
----------------
Fruit Banana
Fruit Apple
Fruit Orange
Fruit Grapes
Veg Potato
Veg Carrott
Veg Lettuce

I need to give each Item in each category a consecutive number, so the above needs to look like:

Category Item Consec Num
-----------------------------
Fruit Banana 1
Fruit Apple 2
Fruit Orange 3
Fruit Grapes 4
Veg Potato 1
Veg Carrott 2
Veg Lettuce 3

I think I need to use some sort of loop, but I can't get my head around it.

This is purely SQL, and I can't edit the database.

Can this be done? I'm using SQL Server 2005?

Cheers,

 

[AshenShugar1873]

SELECT row_number() OVER (Partition BY type
ORDER BY type, item) AS num, type, item
INTO NewTable
FROM test

This assumes the Fruit/Veg column is Type and the Apple/Carrot column is item. This creates a new table, could modify into an update statement if required.

 

[cotton31]

That works great mate, cheers!