October 24, 2008 at 5:16 pm
Okay. I'm stuck. Maybe it's because it's Friday and I'm seeing double, but here's my problem. I have a table that has a roster of individuals that are grouped by sections and within the sections they can be ordered in whatever order a site administrator wants them to be in, but the order is numerical (1, 2, 3 etc.). They want to be able to have a drop-down list next to the user in the roster and select their new order position from that drop-down list. So, if person number one needs to become person number five, then person number two becomes order position 1, 3 becomes 2, 4 becomes 3, 5 becomes 4, 1 becomes 5, and all the rest become unchanged. This also has to work in the opposite direction where if the user wants 5 to become 1, then 1 becomes 2, 2 becomes 3, and so on.
With essentially indefinite number of individuals per roster, I'm not quite sure how to go about doing this. Any ideas?
October 24, 2008 at 6:17 pm
How about ...
1. You make the ordering column as float.
2. When someone changes the ordering of a person, lets say from 1 to 5, you update it as 5.1
and then update the ranking of all using the RANK function ordering on the Order column.
I hope that sounded right 🙂
October 24, 2008 at 7:26 pm
Please post DDL and sample data for your table. (You can see an example of how to do this properly by clicking the link in my signature) How you go about doing this depends a lot on the structure of your table. If your structure isn't up to par, this is going to be incredibly difficult if not impossible to do in any "good" way.
October 25, 2008 at 4:41 pm
This seems like a simple matter, but it actually one of the more notorious traps of client-server programming. Because in fact there is NO simple one-record solution to this problem that behaves correctly. Even using a stored procedure on the SQL Server, it is still necessary for the client and the server to exchange information about multiple records and/or transactional state information on top of the record content information.
The reason for this is that the ordering principle means that the rows in one section cannot be treated independently because they have a associative context that is dynamically dependent on each other. Worse, the client has shared the state of this dynamically dependent associative context with the user and allowed them to modify it. As in any single row case, the client thus has the responsibility of communicating the nature of the users changes to the server and then letting the user know whether this was successful. However, because a change to any record can change the the user-perceived state of all of the rows in the section, the client can NOT just information about a single row and trust that the server can fix up the rest of the rows in the same way that the user is seeing them as displayed by the Client on their screen.
The Client must recognize that the other rows in the section are part of the current row's context, either by dealing with all of them at once, or by dealing with the current row and some context/state management for the section as a whole.
In short, the atomic Entity for the group-member numbering problem in NOT a member row, but the group ("section" in this case) as a whole.
In order to see why this is, consider this case:
1) The section "Owner" for section Q, is reviewing the roster or 6 members and decides to move the member in order #1 to the end, in order #6.
2) Meanwhile, after the section Q Owner has retrieved their list of roster members, but before they have saved their changes, the system Registrar assigns a new member to Section Q and places them at the beginning, in order #1, re-ordering everyone else up one place.
Now what should the Server do when it receives (1)'s request from the client? Just re-order the new #1 to #6? This is surely not what the user expects, or what the client is displaying on the screen. And if it should do anything else, how could it know to do so, if the client only tells it about the former row #1?
Now, whatever answer you have for this situation, try it against this slight modification: what if the Registrar had assigned the new member to the end of the list in position #7 without reordering anyone? Now, what should the server do when it receives the request for member #1 to be changed to #6? Should it make it #6 or #7? And again, how would it know to do anything other than what it normally have done if the Registrar had made no changes during this time?
Once you have supplied the table definitions as Garidin has requested, we can talk about how to do this as a section Entity, rather than as a single row member Entity.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 25, 2008 at 4:52 pm
Oh, and you should supply the DDL for the Section table also, as that will probably be necessary for this discussion.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 27, 2008 at 11:24 am
I have seen is done where you use temporary tables and timestamps, then by grouping and ordering you loop through each one updating the OrderID, but that is probably not the most elegant or efficient. I was wondering if maybe a CLR procedure would work better. This way, I can use arrays and such. I'm not too worried about multiple people adding and reordering as there is only one person that has the ability to add and/or reorder individuals to the roster.
Thanks for your help.
Okay. Here is the table definition. It is definitely not pretty, but I inherited it this way:
/****** Object: Table [dbo].[IMTTeamMember] Script Date: 10/27/2008 12:15:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IMTTeamMember](
[TeamMemberID] [int] IDENTITY(1,1) NOT NULL,
[DirectoryID] [int] NOT NULL,
[TeamID] [int] NOT NULL,
[Section] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email2] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone1Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone2Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone3Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IMTTeamMember_Phone3Type] DEFAULT (''),
[Phone4] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone4Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone5] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone5Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone6] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone6Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Airport1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Airport2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TraineeYN] [bit] NOT NULL CONSTRAINT [DF_IMTTeamMember_TraineeYN] DEFAULT (0),
[AlternateYN] [bit] NOT NULL CONSTRAINT [DF_IMTTeamMember_AlternateYN] DEFAULT (0),
[MemberOf] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Reminder] [int] NOT NULL CONSTRAINT [DF_IMTTeamMember_Reminder] DEFAULT ((0)),
[PhotoID] [int] NULL,
[Unavailable] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AlternateInfo] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderID] [int] NULL,
[DispatchName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DispatchCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Username] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Password] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IMTEmail] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastUpdated] [smalldatetime] NULL CONSTRAINT [DF_IMTTeamMember_LastUpdated] DEFAULT (getdate()),
[RoleID] [int] NULL CONSTRAINT [DF_IMTTeamMember_RoleID] DEFAULT (2),
[Position] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone1Ext] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IMTTeamMember_Phone1Ext] DEFAULT (''),
[Phone2Ext] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IMTTeamMember_Phone2Ext] DEFAULT (''),
[Phone3Ext] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone4Ext] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IMTTeamMember_Phone4Ext] DEFAULT (''),
[Phone5Ext] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IMTTeamMember_Phone5Ext] DEFAULT (''),
[Phone6Ext] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IMTTeamMember_Phone6Ext] DEFAULT (''),
[Deleted] [bit] NOT NULL CONSTRAINT [DF_IMTTeamMember_Deleted] DEFAULT (0),
[GeneralInfo] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_IMTTeamMember] PRIMARY KEY CLUSTERED
(
[TeamMemberID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Determines whether or not the user has been deleted.' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'IMTTeamMember', @level2type=N'COLUMN', @level2name=N'Deleted'
GO
USE [LLC_dev]
GO
ALTER TABLE [dbo].[IMTTeamMember] WITH NOCHECK ADD CONSTRAINT [FK_IMTTeamMember_Directory] FOREIGN KEY([DirectoryID])
REFERENCES [dbo].[Directory] ([DirectoryID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[IMTTeamMember] CHECK CONSTRAINT [FK_IMTTeamMember_Directory]
GO
ALTER TABLE [dbo].[IMTTeamMember] WITH CHECK ADD CONSTRAINT [FK_IMTTeamMember_IMTRoles] FOREIGN KEY([RoleID])
REFERENCES [dbo].[IMTRoles] ([RolesPK])
October 27, 2008 at 11:38 am
Thanks for posting the DDL.
Can you please post some sample data as well?
October 27, 2008 at 11:50 am
I just did an export on some sample data.
TeamMemberID|DirectoryID|TeamID|Section|Email2|Phone1Type|Phone2|Phone2Type|Phone3|Phone3Type|Phone4|Phone4Type|Phone5|Phone5Type|Phone6|Phone6Type|Airport1|Airport2|TraineeYN|AlternateYN|MemberOf|Reminder|PhotoID|Unavailable|AlternateInfo|OrderID|DispatchName|DispatchCode|Username|Password|IMTEmail|LastUpdated|RoleID|Position|Phone1Ext|Phone2Ext|Phone3Ext|Phone4Ext|Phone5Ext|Phone6Ext|Deleted|GeneralInfo
2111|6233|93|Safety||H|555-555-5555|W||M||W||W||W|||False|False|S|0||||1||||||2008-10-17 14:18:00|2||||||||False|
2128|825|93|Plans||W||W||W||W||W||W|||False|True|S|0||||4||||||2008-10-17 14:20:00|2|Other|||||||False|
2129|1867|93|Command||W||W||W||W||W||W|||False|True|S|0||||3||||||2008-10-17 14:20:00|2||||||||False|
2130|3|93|Logistics||W||W||W||W||W||W|||False|True|S|0||||2||||||2008-10-17 14:19:00|2|LLC Asst. Center Manager|||||||False|
2131|167|93|Safety||W||W||W||W||W||W|||False|True|S|0||||5||||||2008-10-17 14:20:00|2|Computer systems interaction designer|||||||False|
2132|1|93|Operations||W||W||W||W||W||W|||False|True|S|0||||6||||||2008-10-17 14:20:00|2|Programmer|||||||False|
2133|1134|93|Information||W||W||W||W||W||W|||False|True|S|0||||7||||||2008-10-17 14:20:00|2|Application Architect|||||||False|
2135|680|93|Finance||W||W||W||W||W||W|||False|True|S|0||||8||||||2008-10-17 14:21:00|2|SCA Intern|||||||False|
2136|2|93|Command||W||W||W||W||W||W|||False|True|S|0||||9||||||2008-10-17 14:21:00|2||||||||False|
2137|6234|93|Operations||W||W||W||W||W||W|||False|True|S|0||||10||||||2008-10-17 14:21:00|2||||||||False|
2138|70|93|Safety||W||W||W||W||W||W|||False|True|S|0||||11||||||2008-10-17 14:21:00|2|Public Information Officer|||||||False|
2139|494|93|Safety||W||W||W||W||W||W|||False|True|S|0||||12||||||2008-10-17 14:22:00|2||||||||False|
2140|2744|93|Plans||W||W||W||W||W||W|||False|True|S|0||||13||||||2008-10-17 14:22:00|2||||||||False|
2141|4309|93|Plans||W||W||W||W||W||W|||False|True|S|0||||14||||||2008-10-17 14:22:00|2|SCA Intern|||||||False|
2142|4|93|Command||W||W||W||W||W||W|||False|True|S|0||||15||||||2008-10-17 14:19:00|2|Manager - Lessons Learned Center|||||||False|
October 27, 2008 at 11:54 am
Posting sample data as demonstrated by the link in my signature has a much higher chance of garnering a response here.
October 27, 2008 at 4:28 pm
Jim Mace (10/27/2008)
I have seen is done where you use temporary tables and timestamps, then by grouping and ordering you loop through each one updating the OrderID, but that is probably not the most elegant or efficient.
Actually, this is a pretty good description of the best way to do it, except that the Server proc should not have to loop, only the client code. Consider:
1) You need timestamps to check for changes after you last retrieved the section-order info.
2) You should have the client do all of the reordering for the section then resave the whole new order list at once. This is more efficient (and easier) than trying to reorder each row one at a time. To provide multi-row information like this you will need either a temp table or XML. A temp table is easier for simple cases (like this one).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply