sort order - reset starting at one, incrementing by one per user

Question

sort order - reset starting at one, incrementing by one per user

amackay_1

Ten Centuries

Points: 1240
More actions
May 20, 2008 at 3:18 pm

#186790

Hi, I have a table containing lists of items that can be sorted by a user. This is a sample of the table layout (with only the pertinant columns showing - other columns show product selected etc.):
UserID SortOrder
1 3
1 5
1 6
2 1
2 3
2 4
2 7
You'll notice that some of the sort numbers are missing - I would like to create a script that updates the SortOrder column for each user to start at 1 and increment by 1 - so after the sort the data in the sample would look like this:
UserId SortOrder
1 1
1 2
1 3
2 1
2 2
2 3
2 4
Please provide some ideas on ways to do this - I'm hoping there is an efficient set-based way to do this so that it can run quickly. Thanks for your help,
Andrew

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

First, not enough data... without an extra column (hopefully, a PK) to identify the sort order, there's no way to do an update because SQL Server will not know which row to update for any given row.

Second, even for a short problem like this, you should get into the habit of posting "executable" data and a Create Table statement... you'll get much better answers quicker. Please see the URL in my signature for how to do that...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

amackay_1 Ten Centuries Points: 1240 More actions · Answer 2

Here is the create table script:

CREATE TABLE [dbo].[rentalsTEST](

[ID] [int] IDENTITY(1,1) NOT NULL,

[userID] [int] NULL,

[movieID] [int] NULL,

[sortOrder] [int] NULL

CONSTRAINT [PK_rentalsTEST] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

amackay_1 Ten Centuries Points: 1240 More actions · Answer 3

Here is the script to insert the test data:

INSERT INTO rentalsTEST

(userID, movieID, sortOrder)

SELECT 1,22,3 UNION ALL

SELECT 1,45,5 UNION ALL

SELECT 1,867,6 UNION ALL

SELECT 2,4,1 UNION ALL

SELECT 2,24,3 UNION ALL

SELECT 2,99,4 UNION ALL

SELECT 2,56,7

Piotr.Rodak SSCrazy Eights Points: 9296 More actions · Answer 4

Here, this works:

update rentalsTest set sortOrder = newSortOrder

from rentalsTest a inner join

(select id, userid, movieid, row_number() over(partition by(userid) order by id) newSortOrder

from rentalsTest) b on a.id = b.id

Piotr

...and your only reply is slàinte mhath

steveb. SSC-Forever Points: 46733 More actions · Answer 5

use ROW_NUMBER ()

SELECT ID, userid ,

ROW_NUMBER() OVER (PARTITION BY userid ORDER BY userid) AS NewOrder

FROM rentalsTest

or as an update

update rentalsTest

set sortorder = NewOrder

FROM (SELECT Id, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY userid) AS NewOrder

FROM rentalsTest) sub

where rentalstest.id = sub.id

--beaten -- took too long writing the code:)

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Heh... see what I mean? Couple hours went by for the original post... 10 minutes after the working table and data was posted in executable form, WHAM! Two good answers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

amackay_1 Ten Centuries Points: 1240 More actions · Answer 7

Thanks guys, just made one change to sort by the original sortOrder field instead of by ID. Worked on the sample data but sometimes the sortorder isn't in the same order as the ID.

update rentalsTest set sortOrder = newSortOrder

from rentalsTest a inner join

(select id, userid, movieid, row_number() over(partition by(userid) order by sortOrder) newSortOrder

from rentalsTest) b on a.id = b.id

Thanks again!

GSquared SSC Guru Points: 260824 More actions · Answer 8

Another solution to this is don't bother updating the base table, just use Row_Number() in the queries if you want to show that sequence.

If you do it the other way, you'll end up with having to update the whole thing every time a customer changes one item. If you just query it, you don't have to do as many updates on as many rows.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon