# Thread: please help: Ordered list design and sort algorithm

1. Registered User
Join Date
Aug 2008
Posts
7

## Unanswered: please help: Ordered list design and sort algorithm

First, apologies for not using whichever thread this most probably is already posted in, im new, didnt understand how to limit the sites searchbox to ansi sql category.

Anyway, I want to create an ordered list with a sortcolumn and then function to increment and decrement the sortvariable for each row. After thinking about it for awhile i realised it was trickier than I first thought and figured that a much better algorithm than I can create is probably out there. Anyone know of one? Or have built their own?

Table: stuff
Column: name(nvarchar)
Column: sort(int)

john 1
fred 2
chris 3

what does the sql look like to move for example fred up or down in the order?

function changesort(fred,up)

thanx for help

nic

2. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
this is not an sql problem, but an application problem (the logic of how to generate the UPDATEs must remain in the app)

but to answer your question, the sql to move fred up would look like this:
Code:
```UPDATE stuff SET sort=1 WHERE name='fred';
UPDATE stuff SET sort=2 WHERE name='john';```
this assumes you are using whole numbers as the sort column, with sequential and consecutive numbers

easier schemes to work with involve using sort numbers in multiples of some large number, e.g. 100 --

john 100
fred 200
chris 300

in this case, moving fred up is easier --
Code:
`UPDATE stuff SET sort=50 WHERE name='fred';`
however, this might require a rare re-sequencing if you run, oh, a hundred or so moves to the same interval

even better is using a FLOAT for the sort, in which case you would never have to resequence, because there is always a float value in between any two float values

3. Registered User
Join Date
Aug 2008
Posts
7
Originally Posted by r937
this is not an sql problem, but an application problem (the logic of how to generate the UPDATEs must remain in the app)

but to answer your question, the sql to move fred up would look like this:
Code:
```UPDATE stuff SET sort=1 WHERE name='fred';
UPDATE stuff SET sort=2 WHERE name='john';```
this assumes you are using whole numbers as the sort column, with sequential and consecutive numbers

easier schemes to work with involve using sort numbers in multiples of some large number, e.g. 100 --

john 100
fred 200
chris 300

in this case, moving fred up is easier --
Code:
`UPDATE stuff SET sort=50 WHERE name='fred';`
however, this might require a rare re-sequencing if you run, oh, a hundred or so moves to the same interval

even better is using a FLOAT for the sort, in which case you would never have to resequence, because there is always a float value in between any two float values
Thanx for answer.

However, you sure there is no way to do it in sql? I was thinking writing a function in the rdbms im using and writing an updatestatement that could manage it? I detest using application for this sortof thing, since basically, all the data needed exist in the database, right? all the application need to supply is the PK and a symbol for "up" or "down" or "movefirst" or somesuch?

The floattip was pretty nifty and ill use it if i dont find a neat int-soloution. Reason I posted was that I assumed there would be an existing algorithm since sortproblems tend to be attractive to the really clever folks.

Mebbe I should ask to have this topic moved to the T-sql section?

4. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
sort problems in sql are handled in exactly one way: by the ORDER BY clause

defining the values that make up the expressions in the ORDER BY clause, that's the role of the application

which T-SQL are you talking about, microsoft's or sybase's? i'll move this thread for you

5. Registered User
Join Date
Aug 2008
Posts
7
thanx, i use sql-server.

6. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
well, both microsoft's and sybase's products are called "sql server" but i'm going toguess you mean the microsoft one

7. Registered User
Join Date
Aug 2008
Posts
7
you are correct, thanx.

btw, i asked a friend of min as well and he tinkered together something like this:

DECLARE @id int
DECLARE @delta int
SET @delta = -1
SET @ID = 1
--------------------

DECLARE @currSort int
DECLARE @targetSort int
DECLARE @ISMAX BIT
DECLARE @ISMIN BIT

SELECT @currSort = sort,@targetSort = (sort+@delta) from tblSortTest WHERE ID = @ID
SELECT @ISMAX = 1 WHERE @currSort = (SELECT MAX(sort) from tblSortTest)
SELECT @ISMIN = 1 WHERE @currSort = (SELECT MIN(sort) from tblSortTest)
SET @ISMAX = ISNULL(@ISMAX,CAST(0 AS BIT))
SET @ISMIN = ISNULL(@ISMIN,CAST(0 AS BIT))
-- buffer

IF @delta > 0 AND NOT @ISMAX = 1
BEGIN
SET @targetSort = dbo.mathMin((SELECT MAX(sort) from tblSortTest),@targetSort)
UPDATE tblSortTest SET sort = -1 WHERE ID = @ID
UPDATE tblSortTest SET sort = (sort-1) WHERE sort >= @currSort AND sort <= @targetSort AND NOT ID = @ID
UPDATE tblSortTest SET sort = @targetSort WHERE ID = @ID
END

IF @delta < 0 AND NOT @ISMIN = 1
BEGIN
SET @targetSort = dbo.mathMax((SELECT MIN(sort) from tblSortTest),@targetSort)
UPDATE tblSortTest SET sort = -1 WHERE ID = @ID
UPDATE tblSortTest SET sort = (sort+1) WHERE sort >= @targetSort AND sort <= @currSort AND NOT ID = @ID
UPDATE tblSortTest SET sort = @targetSort WHERE ID = @ID
END

8. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
i stand by what i said -- there is no good way to do this with sql

what you just posted is a superset of sql, called transact-sql, which allows you to code application logic into a procedure or series of statements, that just happen to be executed by the database engine

maybe i'm splitting hairs

9. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Provided Answers: 1
I think your friend's code is needlessly complicated.

How do you want to allow the users to redefine the sort order? Do you want them to be able to exchange the places of any two records, or do you want them to be able to specify a single record and then bump it up or down one rank?

10. Registered User
Join Date
Aug 2008
Posts
7
Originally Posted by r937
i stand by what i said -- there is no good way to do this with sql

what you just posted is a superset of sql, called transact-sql, which allows you to code application logic into a procedure or series of statements, that just happen to be executed by the database engine

maybe i'm splitting hairs

Well, I understand now that there is no sql for this. Im just not used to making the distincion so clearly.

11. Registered User
Join Date
Aug 2008
Posts
7
Originally Posted by blindman
I think your friend's code is needlessly complicated.

How do you want to allow the users to redefine the sort order? Do you want them to be able to exchange the places of any two records, or do you want them to be able to specify a single record and then bump it up or down one rank?
Well, what i need right now is to just bump them up or down. In this code my friend made provisions for bumping records up and down several steps in one go, but not for trading places which is more than ok for me. Well, the code is probably not optimal, im positive there is some awesome code out there for this problem, this is why i posted the thread! I love perfect algorithms even thou i cant really produce them.

A problem with this code is that if I use explicit sort in more than one set in the database, ideally I would like to use just one stored procedure for all of them. I think it would be reasonable to hardcode the column name in this case, column "sort" and column "id". However, how to use generic tablenames without creating something too icky? I mean, if you have 5 different sets that all use the same sorting strategy, it would be silly to have 5 identical stored procedures? Especially since some cleverdick would get it into his head that one of them procedures is a nifty place to place some extra code a year or two down the line. Then you would have 5 stored procedures that look identical but in fact arent..

12. Registered User
Join Date
Aug 2008
Posts
7
Well, read up on the exec statement. So i guess that would do what i want. But ill suffer with 2 identical sp's for the time being, just easier to wrap my head around right now.

13. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Provided Answers: 1
Code:
```--Create a table
create table SortStuff (name nvarchar(20), sort int)
go

--Add some sample data
insert into SortStuff
(name,
sort)
select 'Alfred', 1
union select 'Bruce', 2
union select 'Chris', 3
union select 'Dan', 4
union select 'Evan', 5
union select 'Fred', 6
go

create procedure BumpSortStuff(@name nvarchar(20), @BumpUp bit)
as
declare	@CurrentSort int
declare	@Direction int
set		@Direction = (@BumpUp * 2) - 1 --Translates @BumpUp to either 1 or -1
set		@CurrentSort = (select sort from SortStuff where name = @name)

update	SortStuff
set		sort =
case
when sort = @CurrentSort then sort - @Direction
when sort = @CurrentSort - @Direction then @CurrentSort
else sort
end
where	name = @name
or sort = @CurrentSort - @Direction
go

exec BumpSortStuff 'Chris', 1 --Bump Chris up
exec BumpSortStuff 'Evan', 0 --Bump Evan down

select * from SortStuff order by sort

drop procedure BumpSortStuff
drop table SortStuff
go```

14. Registered User
Join Date
Apr 2007
Posts
183

## Some serrious bugs in suggestion above

Code:
```CREATE TABLE #SortStuff
(
Name VARCHAR(20),
Sort INT
)
GO

INSERT	#SortStuff
(
Name,
Sort
)
SELECT	'Alfred', 1 UNION ALL
SELECT	'Bruce', 20 UNION ALL
SELECT	'Chris', 31 UNION ALL
SELECT	'Dan',   49 UNION ALL
SELECT	'Evan',  53 UNION ALL
SELECT	'Fred',  66
GO

SELECT		*
FROM		#SortStuff
ORDER BY	Sort
GO

CREATE PROCEDURE BumpSortStuff
(
@Name VARCHAR(20),
@BumpUp BIT
)
AS

SET NOCOUNT ON

DECLARE	@OriginalSort INT,
@NewSort INT

SELECT	@OriginalSort = Sort
FROM	#SortStuff
WHERE	Name = @Name

IF @BumpUp = 1
BEGIN
SELECT	@NewSort = MAX(Sort)
FROM	#SortStuff
WHERE	Sort < @OriginalSort

UPDATE	#SortStuff
SET	Sort = Sort + 1
WHERE	Sort >= COALESCE(@NewSort, @OriginalSort)
END
ELSE
BEGIN
SELECT	@NewSort = MIN(Sort)
FROM	#SortStuff
WHERE	Sort > @OriginalSort

UPDATE	#SortStuff
SET	Sort = Sort - 1
WHERE	Sort <= COALESCE(@NewSort, @OriginalSort)
END

UPDATE	#SortStuff
SET	Sort = COALESCE(@NewSort, @OriginalSort)
WHERE	Name = @Name
GO

EXEC BumpSortStuff 'Chris', 1
GO

SELECT		*
FROM		#SortStuff
ORDER BY	Sort
GO

EXEC BumpSortStuff 'Evan', 0
GO

SELECT		*
FROM		#SortStuff
ORDER BY	Sort
GO

DROP PROCEDURE	BumpSortStuff
GO
DROP TABLE	#SortStuff
GO```

15. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Provided Answers: 1
Some bugs in yours as well:
Code:
```INSERT	#SortStuff
(
Name,
Sort
)
SELECT	'Alfred', 10 UNION ALL
SELECT	'Bruce', null UNION ALL
SELECT	'Chris', 10 UNION ALL
SELECT	'Dan',   null UNION ALL
SELECT	'Evan',  10 UNION ALL
SELECT	'Fred',  null
GO```
We don't know details about his constraints or environment.
There will be serious bugs in any solution without full requirements. I was merely attempting to show an algorithm.

Thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•