Update table increment field

Question

using SQL server 2008.

Hi guys got this query here:

CREATE PROCEDURE [dbo].[GenerateNumbers]
@pg varchar(255)
AS
BEGIN
DECLARE @Counter as int
SET @Counter = 0
UPDATE dbo.paretoMain

SET @counter = NewPareto = @counter +1  
WHERE PG = @pg


END

Works fine, only i want it to do this by a particular order of 1 column. basically order by column 1.

cant seem to figure out how though any ideas?

thanks guys!

EDIT:

Ok seems I've confused people so here goes.

3 columns in a table. 1 called "newpareto" 1 called "Sales" 1 called "part". Sales has currency, part is varchar. Newpareto is blank.

I want to simply 100 numbers for 100 records (example), so when row1, Newpareto = 1. This query i provided does this. However. The 100 Records are not sorted. What i need is To somehow add order by in there to order by Sales column so that the top Sales (example £100.00) will be number 1 in the Newpareto column. Any clearer?

Do you mean that column 1 can be ordered ascending or descending and you want to follow the order, in terms of it being ascending or descending? Do you mean that you want to increment column 1? What data is in column 1? — Michael Harmon, Apr 12 '13 at 11:52
nope i want to put numbers 1-100 lets say in the column called Newpareto. But the order should be order by a feild called column 1, (actually called sales) — lemunk, Apr 12 '13 at 11:53
ok 2 fields, 1st is Sales, 2nd is Newpareto. Sales has values. newpareto hasnt yet. So i run The query i posted and it puts in 1,2,3,4,5,6 etc each row. But if i was to have another column in there "column1", if i wanted it to be sorted by column 1 ascending lets say. how would i edit my query to do this. does that help? — lemunk, Apr 12 '13 at 12:04
I can't figure out a solution. If anyone else can, please jump in. Columns in a SQL table don't have an order, except if you are referring to the physical order of a primary key. I think you are trying to increment "newpareto" by the value of the row in "column1" by one which appears last in the physical order of the table (assuming there is a unique primary key on the Sales column). Is that right? — Michael Harmon, Apr 12 '13 at 12:31
Your question is very unclear, you've shown some code but most of your comments refer to tables and data that you didn't show. Please add some information about the table structures, sample data, and what you expect the data to look like after your `UPDATE`. — Pondlife, Apr 12 '13 at 14:41
Thanks for the update, your explanation is still unclear but I think you're asking [this question](http://stackoverflow.com/questions/3439110/sql-server-update-a-table-by-using-order-by). To avoid confusion, please avoid descriptions and post code instead. In this case, posting a `CREATE TABLE` statement and some `INSERT`s to add test data would be a lot clearer, and it means that people can easily copy and paste it into SSMS to test your problem themselves. — Pondlife, Apr 12 '13 at 16:11

Michael Harmon · Accepted Answer · 2013-04-12T16:58:25.723

0

I believe this may be what you are trying to do:

update paretoMain
set NewPareto=rn
from (select Sales, row_number() over(order by Sales desc) as rn from paretoMain) as x
where paretoMain.Sales=x.Sales

edited Apr 12 '13 at 16:58

answered Apr 12 '13 at 16:50

Michael Harmon

746
5
10

exactly, I eventually figured it out over the weekend. Thanks for the help ill tick yours as the answer – lemunk Apr 15 '13 at 08:31

Update table increment field

1 Answers1