March 31, 2009 at 3:48 am
Hi,
This might be an interesting question.
I have a table of group members which looks a little like:
Group_member_id
Member_id,
Group_id,
Order
All fields are Numeric. Group_member_id is a seeded incrementing PK field.
There are lots of changes going on, so people are being moved about a lot, and this leaves gaps in the order which is very untidy.
So groupX could have 20 members, sequence should be 1 to 20, but could be 5 to 100, with gaps in between.
Currently I programatically cycle through every record once a week, ans resequence the lot. and there are thousands of records.
Is there a way to query the db, and find the group_id's whose members need resequencing?
Is there a way to do the resequencing using a query, without doing it programatically, or using stored procedures?
Thanks and regards,
kinnon
March 31, 2009 at 4:16 am
kinnon_2000 (3/31/2009)
This might be an interesting question.
No kidding, Kinnon!
If these ID's are used by the database and are PK's and FK's, then don't change them - they're there for the database to play with, not you. If the business requires a sequence of numbers
groupX could have 20 members, sequence should be 1 to 20
then add a new column (say, [sequence]) and use that.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 31, 2009 at 4:17 am
Why is it important that there will be no gap? An ID should be used for identification regardless of the next and previous ID. Can you explain why it is so important for you? Also I don’t understand where the gap is (in which column). Could you post the table DDL with insert statement that insert data with gap, and let us know how do you want the data to look after you delete the gap?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 31, 2009 at 4:45 am
Sorry folks, I should have been clearer. :Whistling:
the sequence is in the Order field. no PK's being touched.
Group_member_id is the unique key, group_id, and member_id are refering to keys in other tables.
Only field I'm interested in adjusting is Order, which is the order in which the members are in. Its important, because it relates to the order in which people are contacted, so being able to order the fields is important.
Resequencing the order field is not a functional neccesity, but customers were complaining about the order not being in sequence. its cheeper for us to do the work with a little coded subroutine than to have the application recoded to show a sequence of numbers based on the actual order field values. But the subroutine takes a long time to complete, so was hoping we could minimise the time taken, by detecting which group_id 'Order' field ranges need to be resequenced.
Regards,
kinnon
March 31, 2009 at 4:47 am
There are lots of changes going on, so people are being moved about a lot, and this leaves gaps in the order which is very untidy.
As previously stated don't mix the internal ID with a maybe external one. Your internal ID just needs to be unique it does not matter if there are gaps.
It might be a requirement that every member gets an external ID without any gaps. This should not be handled as the primary key. Use an additional group-member-id table.
Questions:
What happens if one of your members leaves the company?
After this a new member will be hired. What about the sequence?
If previously you had twenty members in one group and the 10th leaves. Does the previously 11th get the 10?
Anyway, back to your question (whose business case I don't understand...) you can handle this with a ROW_NUMBER and a PARTITION:
;WITH t (id, group_id, name) AS
(
SELECT 1, 1, 'g1_m1'
UNION SELECT 2, 1, 'g1_m2'
UNION SELECT 3, 1, 'g1_m3'
UNION SELECT 4, 2, 'g2_m1'
UNION SELECT 5, 2, 'g2_m2'
UNION SELECT 6, 3, 'g3_m1'
)
SELECT *, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY id)
FROM t
Greets
Flo
March 31, 2009 at 4:53 am
Currently I programatically cycle through every record once a week, ans resequence the lot. and there are thousands of records
I dint understand this..Can you explain in detail.
Dint understand what exactly you want to do...
Deinelty it will not cause any problem for you, even though it looks untidy. I use identity column in almost all my tables and use it as a KEY value. Of course I never display it in front end. If you are so adamant that you want it in a proper sequence, you will have to add one more column and fill with proper sequence no.
March 31, 2009 at 4:59 am
Reckon Flo's got the nail on the head here - so all we need is some sample data:
DROP TABLE #GroupMembers
CREATE TABLE #GroupMembers (
Group_member_id INT,
Member_id INT,
Group_id INT,
[Order] INT)
INSERT INTO #GroupMembers (Group_member_id, Member_id, Group_id, [Order])
SELECT 1, 1, 1, 1 UNION ALL
SELECT 1, 2, 1, 3 UNION ALL
SELECT 1, 3, 1, 2 UNION ALL
SELECT 1, 4, 1, 5 UNION ALL
SELECT 1, 5, 1, 4 UNION ALL
SELECT 1, 1, 2, 1 UNION ALL
SELECT 1, 2, 2, 3 UNION ALL
SELECT 1, 3, 2, 2 UNION ALL
SELECT 1, 4, 2, 5 UNION ALL
SELECT 1, 5, 2, 4
SELECT *
FROM #GroupMembers
Kinnon, how about you play with this data and show us exactly what you mean?
Cheers
ChrisM
"Fluffer to Flo"
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 31, 2009 at 5:02 am
Hi, please read my second post. The GUI sequence, and the order these members are contacted in, is in the Order field. not the PK fields. Sorry again, should have been clearer
March 31, 2009 at 5:04 am
lol, cant keep up. let me play and I'll get back to you with example.
March 31, 2009 at 5:10 am
I’m sorry, but I still have few questions. The main question is how do you decide about the order. If I was in one group and moved to a different group what happens to my order? Am I going to be the last in my new group? If a new employee is being added to the group will he always have the last order? There is a good chance that you can use ranking functions and you might won’t need the order column, but we need to know more about your situation and how you decide which employee will get which order.
I strongly recommend that you’ll read the article in this URL – http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D. If you’ll do as suggested in this article, it will help all of us giving you a better answer in a shorter time.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 31, 2009 at 5:19 am
DROP TABLE #GroupMembers
CREATE TABLE #GroupMembers (
Group_member_id INT,
Member_id INT,
Group_id INT,
[Order] INT)
INSERT INTO #GroupMembers (Group_member_id, Member_id, Group_id, [Order])
SELECT 1, 1, 1, 1 UNION ALL
SELECT 1, 2, 1, 8 UNION ALL
SELECT 1, 3, 1, 10 UNION ALL
SELECT 1, 4, 1, 5 UNION ALL
SELECT 1, 5, 1, 4 UNION ALL
SELECT 1, 1, 2, 1 UNION ALL
SELECT 1, 2, 2, 3 UNION ALL
SELECT 1, 3, 2, 2 UNION ALL
SELECT 1, 4, 2, 5 UNION ALL
SELECT 1, 5, 2, 4
SELECT *
FROM #GroupMembers
ORDER BY Group_id, [Order] ASC
Based on ChrisM's example, which was spot on, I've tweaked a few of the order values.
Group 1 has been messed with, and there are gaps in the number sequence of the order field, which the customer uses in order to define the membership order, which for this application is very important, as it deals with automated emergency phone calls.
What the customer sees on screen would be group name, recipient id an name, the order they are in the group, and various other bits.
What I'm hoping for is a query to identify group 1 in the example as needing its order fields updated, and if possible, update them using a query, as my program to fix this takes too long to do the job. There are thousdands of groups in many databases with hundreds of thousands of members spread accross the groups.
As stated, its a minor irritation the customers have picked up on, but I figured it would be an interesting problem to post as my sql server skills are fair, but not great.
Regards,
Allan.
March 31, 2009 at 5:26 am
Apologies again. I'll be clearer in future.
Based on ChrisM's kind example, which was almost spot on, I've tweaked a few of the order values to demonstrate my problem.
DROP TABLE #GroupMembers
CREATE TABLE #GroupMembers (
Group_member_id INT,
Member_id INT,
Group_id INT,
[Order] INT)
INSERT INTO #GroupMembers (Group_member_id, Member_id, Group_id, [Order])
SELECT 1, 1, 1, 1 UNION ALL
SELECT 1, 2, 1, 8 UNION ALL
SELECT 1, 3, 1, 10 UNION ALL
SELECT 1, 4, 1, 5 UNION ALL
SELECT 1, 5, 1, 4 UNION ALL
SELECT 1, 1, 2, 1 UNION ALL
SELECT 1, 2, 2, 3 UNION ALL
SELECT 1, 3, 2, 2 UNION ALL
SELECT 1, 4, 2, 5 UNION ALL
SELECT 1, 5, 2, 4
SELECT *
FROM #GroupMembers
ORDER BY Group_id, [Order] ASC
Group 1 has been messed with, and there are gaps in the number sequence of the order field, which the customer uses in order to define the membership order, which for this application is very important, as it deals with automated emergency phone calls.
What the customer sees on screen would be group name, recipient id an name, the order they are in the group, and various other bits.
What I'm hoping for is a query to identify group 1 in the example as needing its order fields updated, and if possible, update them using a query, as my program to fix this takes too long to do the job. There are thousdands of groups in many databases with hundreds of thousands of members spread accross the groups.
As stated, its a minor irritation the customers have picked up on, but I figured it would be an interesting problem to post as my sql server skills are fair, but not great.
Regards,
Allan.
March 31, 2009 at 5:30 am
sorry, having a bad day. didnt think it posted first time. delete isnt working for me so cant remove duplicate. :crazy:
March 31, 2009 at 5:37 am
Allan, to reiterate Ade The main question is how do you decide about the order. what do you want the values in the [order] column to be dependant upon?
The renumbering is easy - you will kick yourself (particularly since this is 2k5) - but we need to know the rules!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 31, 2009 at 5:45 am
Hi,
Order is determined by the customer via the gui.
if the user adds a member to a group using the gui, the default order is the last order +1. in our example, the next order value would be 11 for group 1.
When people are moved around, we end up with gaps in the order numbering.
If the order fields for group 1 are 1, 4, 5, 8, 10; I would like them to be 1, 2, 3, 4, 5. So it looks nice and neat on the front end.
Sorry again for the messy post, and thanks.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply