Results 1 to 15 of 15
  1. #1
    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. #2
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2008
    Posts
    7
    Quote 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. #4
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

  6. #6
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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. #8
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Aug 2008
    Posts
    7
    Quote 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. #11
    Join Date
    Aug 2008
    Posts
    7
    Quote 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. #12
    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. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    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. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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