Help! How to build "ordering"? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help! How to build "ordering"?

Author

Topic

dbwilson4
Yak Posting Veteran

50 Posts

Posted - 2007-06-28 : 16:12:50

How do I build an "order" of objects?
Like users have Videos. I want to allow them to order their videos easily by moving ahead or behind the next/previous video.

ALOT of my application objects have this type of ordering.

How do I build the "SQL string" required for ordering? Also, I'm guessing it has to be in a transaction to be "accurate" and make sure there are no offsets? Also, will it be performant?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-28 : 17:26:29

do you mean order as in 2 comes after 1? or do you mean order as in "can I take your order?"

dbwilson4
Yak Posting Veteran

50 Posts

Posted - 2007-06-28 : 17:51:48

2 comes after 1 type of ordering with 100% accuracy (no offsets).
What I mean by 100% accuracy is that if theres 10 items, an items order cannot exceed 10 or be less than 0 and two items cannot be the same order either.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-28 : 18:36:31

are you familar with the ORDER BY clause in sql?

dbwilson4
Yak Posting Veteran

50 Posts

Posted - 2007-06-28 : 22:01:46

Yes but how do I order BASED ON USER CUSTOM ORDER.

For example:
User has a bunch of albums. He wants to order them by 0,1,2,3 etc. He may later switch Album A from order 1 to 8. Anything in ORDER 8 gets pushed to 7, anything in 7 gets pushed to 6, anything in 6 gets pushed to 5 etc.

I have no idea how to do this. It's complex sql query.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-29 : 00:25:36

if you are swapping @x and @y, you could do the following:

I have no idea what your table looks like since you didn't post DDL, but you get the idea from the below.


declare @t table (item varchar(10), orderCol int, userId int)

insert into @t
select 'aaa', 1, 1 union all
select 'bbb', 2, 1 union all
select 'ccc', 3, 1 union all
select 'ddd', 4, 1 union all
select 'eee', 1, 2 union all
select 'fff', 2, 2

-- see what we have
select * from @t

-- now switch items 1 and 2 for user 1
-- you should put this logic in a proc
declare @swap1 int, @swap2 int, @userId int
set @swap1=1
set @swap2=2
set @userId=1

update @t
set orderCol =	case 
                  when orderCol=@swap1 then @swap2 
                  when orderCol=@swap2 then @swap1 
                  else orderCol
                end
where userId=@userId and orderCol in (@swap1,@swap2)

-- verify they are switched
select * from @t

dbwilson4
Yak Posting Veteran

50 Posts

Posted - 2007-06-29 : 11:45:09

While your code does work, it does not guarantee that the next ORDERING number will be an offset (like 3,4,10 instead of 3,4,5) and also it was incorrect when you passed in swap ids of 4 and 5

I formulated a better query below
UPDATE album SET [order] = ( (( SELECT RowNumber FROM (SELECT id, userid, ROW_NUMBER() OVER (order by id) AS RowNumber FROM album WHERE userid = 1) as tbl WHERE tbl.id = album.id)-1) + 1 ) WHERE userid = 1 AND id = 16260600

This works fine. In fact, if you wanted to update the whole "userid 1's" records, you could just take out the id filter on that query.

The problem is, what if a user deletes a record? Then there would be offsets again. The only way to ensure no offsets is have a filter view and query by that OR run a "row number update" for userid = 1 everytime user id 1 deletes a record.

What do you think? I want as maximum performance possible.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-29 : 12:01:14

indeed my code is broken if one of the values to swap doesn't exist. you could just add a check there to raise an error if one of the values doesn't exist.

to me it seems a strange requirement that you shouldn't have any gaps in the orderCol. after all this int is just for ordering, right? to keep it gapless requires a lot of tedious and error-prone bookkeeping, seems to me. If you didn't care about gaps, deletes would be trivial. also you could use ints that have large gaps to begin with so inserts would rarely cause an update to several rows.

if it's to be displayed in some UI, it might make more sense to use ROW_NUMBER() to generate the int you want to display, rather than displaying the orderCol value, similar to what you are doing above.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources