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.
| Author |
Topic |
|
rasmasyean
Starting Member
22 Posts |
 Posted - 2009-07-01 : 22:07:52
|
| How would I go about doing something like this?I have n groups which each have m members.I want preserve the order of my groups in the database as I manually rearrange them and insert/delete new groups.I also want to preserve the order of the members within each group as I manually rearrange them and insert/delete new members.If I use like a “group_position” field and “member_position” field with 1-n and 1-m respectively, I can use code to propagate new positions as I insert/delete groups/members, right?The problem with this is that if n and m becomes like 1,000,000 I would be doing a lot of updates.Is there some “automatic feature” can preserve order of records like this? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-01 : 22:18:36
|
quote:
I want preserve the order of my groups in the database as I manually rearrange them and insert/delete new groups.
You shouldn't. The sequence or order of records stored in database is not important. When you retrieve the records, you specify the sequence of how the records is returning to you by using ORDER BY clause. You should not care how SQL server stored your records in the database.quote:
I also want to preserve the order of the members within each group as I manually rearrange them and insert/delete new members.
Same comment as above.quote:
If I use like a “group_position” field and “member_position” field with 1-n and 1-m respectively,
Yes you could have a group_position or member_position in the table. So when you retrieve the records, you can specify the sequence with it.exampleselect * from [groups] order by [group_position] quote:
I can use code to propagate new positions as I insert/delete groups/members, right?
Yes. you can update the group_position / member_position to new ordering / sequencing.Before going any further into details . . . What is your requirement for the group_position / member_position ? What is the significant of this ? What is the position based on ? Any rules to determine the position ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
rasmasyean
Starting Member
22 Posts |
Posted - 2009-07-01 : 22:46:02
|
| There isn’t really any rule to determine position.I can think of a situation where this applies.Like let’s say there are CPU “jobs”. And each of those jobs have “tasks”.If I want to rearrange the order of which the jobs are fed to the CPU and also rearrange the order of which the tasks are performed by the jobs… |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-01 : 22:51:00
|
quote:
Originally posted by rasmasyean
There isn’t really any rule to determine position.I can think of a situation where this applies.Like let’s say there are CPU “jobs”. And each of those jobs have “tasks”.If I want to rearrange the order of which the jobs are fed to the CPU and also rearrange the order of which the tasks are performed by the jobs…
What you just describe there with the CPU jobs is the rule.Assuming you want to records in the sequence of when the group is created, add a column to the group table to store when the record is created.select *from groupsorder by create_date And if you want the latest first, just add a DESC to the ORDER BY.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
rasmasyean
Starting Member
22 Posts |
Posted - 2009-07-02 : 01:29:24
|
quote:
Originally posted by khtan
quote:
Originally posted by rasmasyean
There isn’t really any rule to determine position.I can think of a situation where this applies.Like let’s say there are CPU “jobs”. And each of those jobs have “tasks”.If I want to rearrange the order of which the jobs are fed to the CPU and also rearrange the order of which the tasks are performed by the jobs…
What you just describe there with the CPU jobs is the rule.Assuming you want to records in the sequence of when the group is created, add a column to the group table to store when the record is created.select *from groupsorder by create_date And if you want the latest first, just add a DESC to the ORDER BY.
KH[spoiler]Time is always against us[/spoiler]
Actually, what I meant was there was no “logical rule”. The rule that you provided was assuming that you want to run the jobs in FIFO.But what I’m saying is that when these jobs and tasks are lined up, someone can create a new job and insert it into a random place (cutting the line).And then you can even re-order it if you all of the sudden feel you have to run job #500 after job#10. So job #11 becomes job #12, job #12 becomes job #13,…and job #499 becomes job #500. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-07-03 : 22:58:02
|
| You definitely need a position number on your table.If you have a GUI you can move items up & down one at a time it's easy - just swap the order number of 2 records.If (and I think this is more the case) you have to reorder in big chunks then I suggest you keep your row numbers with a wide gap (say 1000). This allows you move things around without doing a massive number of updates by picking a slot in between the ones either side of its new position. This then leaves you with 2 options. First option is to run a job periodically to reassign the numbers to ensure there are always gaps. The second is to be a bit clever and work out the minimum cascade of updates by renumbering prior or subsequent records. Depending on how many times you re-arrange it though, eventually you may end up with a sequence and renumbering the whole lot.You can mitigate the cost of re-ordering groups by pulling the group order out of your main record and into a separate group table. This allows you to maintain the group sequence once per group, not in every record in every group saving potentially a large number of updates, i.e. you have the same problem but on the set of groups, not the entire set. |
|
|
|
|
|
|
|
|
Automatic sequence labeling of records?