AutoSorting

• Elizabeth Martin

  SSC Enthusiast

  Points: 193

  More actions

  March 22, 2006 at 12:18 pm

  #112343

  To begin, I am using Access along with SQL Server.

  I have a table with Projects in it.  This table has a column named Priority, where the Projects are prioritized.  The priorities range from approximately 1 to (no greater than)150.  And the projects that are not prioritized yet have a priority of 999.

  Here is my question:  I would like for the user to be able to change the priority of a project and the stored procedure to automatically sort the rest of the projects accordingly.  For example, if you change a project's priority from 10 to 5, it would need the current project with a priority of 5 to move to 6, the current project with a priority of 6 to move to 7, and so on up to 10. 

  Also, there might be gaps in the priorities.  For example, if you change a project's priority from 10 to 5, but there is no priority 7, it would only need to move 5 to 6, and 6 to 7, but not change the rest.

  Can anyone help with this?  I am using Access and SQL Server.  I am looking for a stored procedure called from Access to do this.

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  March 23, 2006 at 4:30 am

  #628304

  Hi Elizabeth,

  This should give you some ideas and get you started...

  --prepare and show example data

  declare @project table (id smallint identity(1, 1), priority int)

  insert @project

        SELECT 4

  UNION SELECT 5

  UNION SELECT 6

  UNION SELECT 8

  UNION SELECT 9

  UNION SELECT 10

  UNION SELECT 14

  select * from @project order by priority

  --inputs (example)

  declare @ProjectId int

  declare @NewPriority int

  set @ProjectId = 6 --i.e. priority 10

  set @NewPriority = 5

  --shift other projects

  if exists (select * from @project where priority = @NewPriority)

  begin

      declare @EndPriority int

      select @EndPriority = min(a.priority) from @project a left outer join @project b on a.priority = b.priority - 1 where a.priority >= @NewPriority and b.priority is null

      update @project set priority = priority + 1 where priority between @NewPriority and @EndPriority

  end

  --shift this project

  update @project set priority = @NewPriority where Id = @ProjectId

  --show results

  select * from @project order by priority

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.