sequence in a range

Question

sequence in a range

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

kinnon_2000 (3/31/2009)
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.

So the new sequence is the same ORDER as the old sequence, only starting at 1 and with no gaps?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

kinnon_2000 Say Hey Kid Points: 702 More actions · Answer 2

Chris Morris (3/31/2009)
So the new sequence is the same ORDER as the old sequence, only starting at 1 and with no gaps?

Yup :w00t:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Flo's already written it for you, Allan. Do you know how to do an UPDATE...FROM...?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

kinnon_2000 (3/31/2009)
sorry, having a bad day. didnt think it posted first time. delete isnt working for me so cant remove duplicate. :crazy:

If I now understand you correctly, you just want to order the records by group_id and for each group by Order. The fact that the values in the column Order have gaps in it is not relevant for the records ordering (e.g. you still use it as the base of the order). You simply don’t want to see gaps in that column. If this is the case, there is no need to modify the values of that column. You can use the column order as the input column for row_number() function. Take a look at this code that uses your example:

select Group_member_id, Member_id, Group_id,

row_number() over (partition by Group_id order by [order]) as OrderNum

from #GroupMembers

order by Group_id, [Order]

Is this is what you are after?

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/

kinnon_2000 Say Hey Kid Points: 702 More actions · Answer 5

Hi, been away so apologies for the delay in getting back to this.

Adi, are on the button with this.

The last bit is, I want to modifty the field called Order, so that it contains the OrderNum values.

Spent some time playing with update statements to try and do this. no joy yet.

So far my update query, which dosnt work, looks like:

UPDATE #GroupMembers

SET [Order]=OrdNum FROM (

SELECT row_number()

over (partition by Group_id order by [order]) AS OrdNum

from #GroupMembers

)

order by Group_id, [Order]

Can anyone correct me?

Thanks again,

Allan

Vijaya Kadiyala SSCrazy Eights Points: 9149 More actions · Answer 6

The point which i didn't understand is why your code depends on the sequence of the PK??

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

kinnon_2000 Say Hey Kid Points: 702 More actions · Answer 7

The order of the PK is not important.

Heres a better example maybe:

DROP TABLE #GroupMembers

CREATE TABLE #GroupMembers (

Group_member_id INT,

Member_name VARCHAR(10),

Group_name VARCHAR(3),

[Order] INT)

INSERT INTO #GroupMembers (Group_member_id, Member_name, Group_name, [Order])

SELECT 1,'Barry','LON', 1 UNION ALL

SELECT 2,'John','LON', 8 UNION ALL

SELECT 3,'Joe','LON', 10 UNION ALL

SELECT 4,'Sam','GLA', 5 UNION ALL

SELECT 5,'Bob','GLA', 20 UNION ALL

SELECT 6,'Desmond','GLA', 1 UNION ALL

SELECT 7,'Matt','LON', 3 UNION ALL

SELECT 8,'Lou','BIR', 2 UNION ALL

SELECT 9,'Murdo','BIR', 5 UNION ALL

SELECT 10,'Derek','GLA', 4

Instead of id's referring to fk's on other tables, lets just say we have the data above, with member names and group names, and an order column. Group_member_id is the pk, and we ignore it.

Based on Adi's query, see the following:

select Member_name, Group_name, [Order],

row_number() over (partition by Group_name order by [order]) as OrderNum

from #GroupMembers

order by Group_name, [Order]

The order, although in order, is not in a sequence without gaps. The above query is bang on the button. I'm just trying to update the content of Order field with the value of OrderNum in the query above.

So what I'm trying to get working is something like

UPDATE #GroupMembers

SET [Order]=OrdNum FROM(

SELECT row_number() over (partition by Group_name order by [order]) AS OrdNum FROM #GroupMembers

)

Regards,

Allan.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Do you mean this?

DROP TABLE #GroupMembers

CREATE TABLE #GroupMembers (

Group_member_id INT,

Member_name VARCHAR(10),

Group_name VARCHAR(3),

[Order] INT)

INSERT INTO #GroupMembers (Group_member_id, Member_name, Group_name, [Order])

SELECT 1,'Barry','LON', 1 UNION ALL

SELECT 2,'John','LON', 8 UNION ALL

SELECT 3,'Joe','LON', 10 UNION ALL

SELECT 4,'Sam','GLA', 5 UNION ALL

SELECT 5,'Bob','GLA', 20 UNION ALL

SELECT 6,'Desmond','GLA', 1 UNION ALL

SELECT 7,'Matt','LON', 3 UNION ALL

SELECT 8,'Lou','BIR', 2 UNION ALL

SELECT 9,'Murdo','BIR', 5 UNION ALL

SELECT 10,'Derek','GLA', 4

UPDATE g SET [Order] = d.OrderNum

FROM #GroupMembers g

INNER JOIN (SELECT Group_member_id,

row_number() over (partition by Group_name order by [order]) as OrderNum

FROM #GroupMembers

) d ON d.Group_member_id = g.Group_member_id

SELECT * FROM #GroupMembers ORDER BY Group_name, [Order]

Output:

Group_member_id Member_name Group_name Order

--------------- ----------- ---------- -----------

8 Lou BIR 1

9 Murdo BIR 2

6 Desmond GLA 1

10 Derek GLA 2

4 Sam GLA 3

5 Bob GLA 4

1 Barry LON 1

7 Matt LON 2

2 John LON 3

3 Joe LON 4

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

kinnon_2000 Say Hey Kid Points: 702 More actions · Answer 9

BINGO!! :w00t:

Chris, you are da man!

This is perfect. 😀

Thank you.

Regards,

Allan.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

No worries Allan, though I think it's Adi who deserves the credit.

Have a read of this when you've got a few minutes.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

kinnon_2000 Say Hey Kid Points: 702 More actions · Answer 11

Totally. thanks Adi and Flo, Thanks all who contributed.

Sorry again for my poor question post. will keep it clearer in future.

Allan.