SQL Server 2000 Related Query

Question

SQL Server 2000 Related Query

venkatv.venkat1

Valued Member

Points: 59
More actions
September 26, 2009 at 10:08 am

#99878

Hi All,
Can any one help me out on my requirement.we have MS SQL Server 2000 in our environment.
I sincerely apologize as i am posting a topic in SQL Server 2005, but as i am new i was unable to find the older version related postings, so i am posting here. Please consider.
I have a table which has 3 columns
say
colA, colB, colC where colA, colB are associated as many-to-many.
Ex:
colA colB
54 19
54 15
56 20
58 20
58 18
58 16
what i need is based on the association i need to update the colC value as 1,2,3 soo on
that is:
colA colB colC
54 19 1
54 15 2
56 20 1
58 20 1
58 18 2
58 16 3
It would be a great favour if any one help me out on this.
--Venkat.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

It's not possible unless you have another column that maintains the order of the original data or you aways want the data in descending order according to column B (according to the data you posted).

Is that what you want? The data to be sorted/numbered in descending order for each value of Column A according to Column B?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

venkatv.venkat1 Valued Member Points: 59 More actions · Answer 2

Hi Jeff,

Thanks for the reply.

As of now we are not looking for specific sorting on the numbers in colC, but if it gets the ascending order data based on the colA.

Ex:

colA col B colC

22 46 1

22 48 2

22 44 3

22 55 4

24 19 1

24 28 2

25 46 1

26 24 1

So what exactly the requirement is they want to group the colA values and compare with colB and display the sequence values like 1,2,3,4 in the "22" related data.

I got to know that in SQL Server 2005 we can use row_number and partition to achieve this, but i am using sql server 2000, which doesn't support the row_number and i guess partition also.

they need it the order as 1,2,3,4 (asc), not the desc (4,3,2,1).

please help me out on this.

Thanks,

Venkat.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

You've missed my point. I realize that Column C needs to be in ascending order. Does the order in which they appear according to column B need to be maintained in any way shape or fashion? If so, what maintains that order?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

venkatv.venkat1 Valued Member Points: 59 More actions · Answer 4

Hi Jeff,

The order can be anything which it looks at column B. (irrespective of any order).

Regards,

Venkat.

venkatv.venkat1 Valued Member Points: 59 More actions · Answer 5

Hi Jeff,

Did you get a chance to look at the query, i mean is there any possibility to get the query written in SQL SERVER 2000 of such requirement.

Just felt enthusiastic to ask and also want to make sure whether i was able to answer your question in the right way.

Regards,

Venkat.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

What I'm concerned about is the apparent lack of detail in the requirements they gave you. I just can't imagine that they don't care about the sort order of this task.

Also, to help you better in the future without having to go back and forth with questions, please take the time to read the following article carefully. If you follow the recommendations in that article, I guarantee that your future posts will be answered with fully tested code and much, much quicker.

Here's the code I came up with based on the data you provided and the interpretation of the requirements you provided.... note that having the correct clustered index on the table is absolutely required or this will crash and burn very badly...

--===== Create and populate a demo table.
     -- This is NOT a part of the solution.
     -- Using a temp table here just so it doesn't
     -- get in the way. It's supposed to represent
     -- your current table.
     -- This is also the way you should present your
     -- data on future posts.
 CREATE TABLE #YourTable
        (
        ColA INT,
        ColB INT,
        ColC INT
        )

 INSERT INTO #YourTable
        (ColA, ColB)
 SELECT 22, 46 UNION ALL
 SELECT 22, 48 UNION ALL
 SELECT 22, 44 UNION ALL
 SELECT 22, 55 UNION ALL
 SELECT 24, 19 UNION ALL
 SELECT 24, 28 UNION ALL
 SELECT 25, 46 UNION ALL
 SELECT 26, 24

--===== This part IS a part of the solution. I don't know
     -- what the clustered index on the actual table is,
     -- but you need one in the order that you expect the
     -- numbering to occur on ColC.  If you can't make it
     -- or change an existing one, then you need to copy 
     -- the data to a temp table where you can make one.
     -- It would be nice if you actually told use what the
     -- PK is and what the clustered index is.
 CREATE CLUSTERED INDEX IX_YourTable_ColA
     ON #YourTable (ColA)

--===== Ready to rock... everything is a part of the solution
     -- from here on.

--===== Declare some obviously named variables
DECLARE @PrevColA    INT,
        @ColCCounter INT

 UPDATE #YourTable
    SET @ColCCounter = ColC = CASE WHEN ColA = @PrevColA 
                                   THEN @ColCCounter + 1
                                   ELSE 1
                                   END,
        @PrevColA    = ColA

--===== Verify the results.  Keep in mind that you said
     -- that any order of column "B" is fine.  The order
     -- of ColB cannot be preserved for there is some 
     -- other column we don't know about.  Like I said,
     -- it would be real handy to know what the existing
     -- indexes and keys on this table are.
 SELECT *
   FROM #YourTable
  ORDER BY ColA,ColC

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

venkatv.venkat1 Valued Member Points: 59 More actions · Answer 7

Hi Jeff,

Thanks for the Query, I appreciate your help. It Worked very well.

Regards,

Venkat.

DougGifford SSCommitted Points: 1815 More actions · Answer 8

This code is great.

Now let's assume we want ColC to be sequenced in ColA ascending, ColB ascending order.

How would your code be modified to accomplish that?

2244NULL

2246NULL

2248NULL

2255NULL

2419NULL

2428NULL

2546NULL

2624NULL

"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"

DougGifford SSCommitted Points: 1815 More actions · Answer 9

I think I got my answer.:-)

Just change the clustered index to:

CREATE CLUSTERED INDEX IX_YourTable_ColA

ON #YourTable (ColA,ColB)

22441

22462

22483

22554

24191

24282

25461

26241

"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 10

Sorry... lost track of this thread.

Yes, changing the clustered index would do the trick. Now you know why I was asking all the questions about what "order" these things should actually be in. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)