Grouping and update problem - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping and update problem

Author

Topic

Dargon
Starting Member

26 Posts

Posted - 2005-09-21 : 09:08:18

Hi, folks!
The problem I got looks simple, but I got confused how to resolve it. I have table which holds some product info ordered by sequence number.
i need to group it by productName and update sequence so it should match the new order.

ProductName SEQUENCENumber Description
-------- ----------- -----------
A 11 D1
A 12 D2
B 13 D3
A 14 D4
B 15 D5
C 16 D6
B 17 D7
C 18 D8
C 19 D9

The final table should look like this:

COLUMN_1 SEQUENCE_ID Description
-------- ----------- -----------
A 11 D1
A 12 D2
A 13 D4
B 14 D3
B 15 D5
B 16 D7
C 17 D6
C 18 D8
C 19 D9

I think that I have to use cursor, but can not get the correct result. Any help is appreciated.
Thanks,
dargon

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-21 : 09:24:45

No Cursor Required

--*****************************************************************
CREATE TABLE #MyTab(ProductName VARCHAR(1), SEQUENCENumber INT, Description VARCHAR(20))

INSERT INTO #MyTab
SELECT 'A', 11, 'D1' UNION ALL
SELECT 'A', 12, 'D2' UNION ALL
SELECT 'B', 13, 'D3' UNION ALL
SELECT 'A', 14, 'D4' UNION ALL
SELECT 'B', 15, 'D5' UNION ALL
SELECT 'C', 16, 'D6' UNION ALL
SELECT 'B', 17, 'D7' UNION ALL
SELECT 'C', 18, 'D8' UNION ALL
SELECT 'C', 19, 'D9'

CREATE TABLE #MyTabWrk(ProductName VARCHAR(1), SEQUENCENumber INT IDENTITY(11, 1), Description VARCHAR(20))
INSERT INTO #MyTabWrk(ProductName, Description)
SELECT
ProductName,
Description
FROM
#MyTab
ORDER BY
ProductName,
Description

SELECT * FROM #MyTabWrk
ORDER BY
ProductName,
Description

DROP TABLE #MyTab
DROP TABLE #MyTabWrk
--***************************************************************************************

Duane.

Dargon
Starting Member

26 Posts

Posted - 2005-09-21 : 09:48:07

Thanks for answer. The problem is that I do not know the first sequence number, so I have to read it from table. Is it any simple way to pass is to new table? I am new in SQL...

dargon

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-21 : 10:01:46

I fthis is a once off "Fix" that you are running then the sequence number can be obtained like this
select min(sequenceno) from table

That would then obviously be the value used in the starting point for the identity column

Duane.

Dargon
Starting Member

26 Posts

Posted - 2005-09-21 : 10:35:36

I tried create variable to store seq numbet like this:

Declare @seq int
Set @seq=(Select min(SEQUENCENumber) from #MyTab),
but i can not place @seq into table #MyTabWrk declaration, it gives syntax error. Another problem is that order by Product name does not give the right result if C<B. If for example initial table is:

ProductName SEQUENCENumber Description
-------- ----------- -----------
A 11 D1
A 12 D2
C 13 D3
A 14 D4
B 15 D5
C 16 D6
B 17 D7
C 18 D8
C 19 D9

The final table should look like this:

COLUMN_1 SEQUENCE_ID Description
-------- ----------- -----------
A 11 D1
A 12 D2
A 13 D4
C 14 D3
C 15 D6
C 16 D8
C 17 D9
B 18 D5
B 19 D7

So groups should be in the same order, like they first appear in initial table.

dargon

Dargon
Starting Member

26 Posts

Posted - 2005-09-21 : 16:07:50

Anyone has an idea how to implement this? Please!!!
dargon

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-22 : 03:56:27

How about this ?

CREATE TABLE #MyTab(ProductName VARCHAR(1), SEQUENCENumber INT, Description VARCHAR(20))

INSERT INTO #MyTab
SELECT 'A', 11, 'D1' UNION ALL
SELECT 'A', 12, 'D2' UNION ALL
SELECT 'B', 13, 'D3' UNION ALL
SELECT 'A', 14, 'D4' UNION ALL
SELECT 'B', 15, 'D5' UNION ALL
SELECT 'C', 16, 'D6' UNION ALL
SELECT 'B', 17, 'D7' UNION ALL
SELECT 'C', 18, 'D8' UNION ALL
SELECT 'C', 19, 'D9'

Declare @seq int
declare @sqlstr varchar(4000)

Set @seq=(Select min(SEQUENCENumber) from #MyTab)

set @sqlstr = '
CREATE TABLE #MyTabWrk(ProductName VARCHAR(1), SEQUENCENumber INT IDENTITY(' + str(@seq) + ', 1), Description VARCHAR(20))
INSERT INTO #MyTabWrk(ProductName, Description)
SELECT
ProductName,
Description
FROM
#MyTab
ORDER BY
ProductName,
Description

SELECT * FROM #MyTabWrk
ORDER BY
ProductName,
Description

DROP TABLE #MyTabWrk'

exec(@sqlstr)
drop table #MyTab

Duane.

Dargon
Starting Member

26 Posts

Posted - 2005-09-22 : 08:56:15

ditch,thanks,
but your solution still keeps groups ordered by ProductName. btw, I think that I know how to make it
Here is what I got(thanks, tpg!):

declare @t table (ProductName varchar, [SEQUENCE] int, [Description] varchar(2))
declare @Res table (ProductName varchar, [SEQUENCE] int, [Description] varchar(2), i int identity primary key)
declare @MinSEQUENCE int

insert @t values('A', 11, 'D1')
insert @t values('A', 12, 'D2')
insert @t values('D', 13, 'D3')
insert @t values('A', 14, 'D4')
insert @t values('B', 15, 'D5')
insert @t values('C', 16, 'D6')
insert @t values('B', 17, 'D7')
insert @t values('C', 18, 'D8')
insert @t values('C', 19, 'D9')

select @MinSEQUENCE = min([SEQUENCE]) from @t
insert @Res (ProductName, [SEQUENCE], [Description])
select t1.*
from @t t1 join
(select ProductName,
min([SEQUENCE]) as min_SEQUENCE
from @t
group by ProductName) t2 on t1.ProductName = t2.ProductName
order by t2.min_SEQUENCE

update @Res set [SEQUENCE] = @MinSEQUENCE + i - 1

select ProductName,
[SEQUENCE],
[Description]
from @Res order by 2

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-22 : 09:15:38

Sequence numbers don't really belong in a relational database if it is not derived naturally from the data. It seems like, in this case, it will be constantly changing -- what happens if you get another row for 'A' after you'd assigned your sequences? Don't they all need to change?

Why is this sequence number necessary to store in the table? Is it possible just to display the sequence number (if needed) on reports or when you return the data to the client dynamically instead of storing it in the table?

Dargon
Starting Member

26 Posts

Posted - 2005-09-22 : 15:15:46

Actually, in this case Sequence is just specific order for all records in the table, bad name, I agree. I will not get any data here after these numbers are assigned. The number reflects when this record was placed into table, but in some cases, I needed to group it by ProductName.
dargon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources