SQL Server 2000 : generating and incrementing data from column conditionally without using CURSOR

Question

:)

Is there any way to create an index, and incrementing with a given condition, but without CURSOR handling/usage

For example:

sql_tables

The condition in my case is that: "if the current color (this is the item to be checked) is the same as the last one: not increment, otherwise increment in one unit"

This must be in a SQL query with no CURSOR USAGE and of course a good time (work with ... 10000 rows at least)

Thanks in advance.

EDIT: I forgot to mention that NEW_INDEX Column doesn't exist. It must be generated with the with the query.

EDIT2: Is there a way that only make use of SELECT/INSERT/UPDATE statements? (not set, declare...)

When you say *...same as the **last one** ...* you are implying an ordering - order by what criteria?? Simply by increasing `ID` ? Or something else? — marc_s, Dec 30 '11 at 15:10
What do you base last one? Do you mean by ID # ?? What is the ordering here, how can I tell if prev == current? — JonH, Dec 30 '11 at 15:10
SQL Server 2000 eliminates many of the ways to accomplish this, ROW_Number() and CTEs, but look at this: http://stackoverflow.com/q/967054/65223 — KM., Dec 30 '11 at 15:13
Avoiding a cursor may be more effort that it's worth here - because the `NEW_INDEX` value assigned to a particular row requires the `NEW_INDEX` of *every* "previous" row to have been calculated (either on a row-by-row basis, or re-computed for each row within the set if performing a set-based update). You can write a `WHILE` loop that avoids the *word* `CURSOR` appearing, but again may be more effort that it's worth. — Damien_The_Unbeliever, Dec 30 '11 at 15:14
@marc_s The criteria is that is the color of the actual is the same as the before then not increment the NEW_INDEX, otherwise increment it. — crsuarezf, Dec 30 '11 at 15:29
@JonH when the prev color is equal to the actual one not increment, otherwise increment. :) — crsuarezf, Dec 30 '11 at 15:37
@ingcarlos - we understand that but what marc_s is asking is very valid....by what ordering is the next one??? Think about it how does one determine what the next row is. Remember ordering is not defined without using a proper `ORDER BY` clause, just because you see the ID's in ascending order does not mean it's always going to be that way. — JonH, Dec 30 '11 at 15:39
@ingcarlos - the point people are trying to make to you is that tables don't have *any* inherent order - if there's some ordering requirement (as you seem to need, because you're referring to previous/before), you have to define what that ordering is (e.g. define that the previous row always has an `ID` one lower than the current `ID` value - if so, that also indicates that we *don't* have to deal with gaps in `ID` numbers...) — Damien_The_Unbeliever, Dec 30 '11 at 15:41
Ok, @JonH. Lets assume that the ID is identity not-null, and primary key :) and the order is by this one column too (I think that if we dont put any order, the DB assumes creation record order or something like that, am i rite?) — crsuarezf, Dec 30 '11 at 16:42
No you are not right, you should never assume ordering. What happens when you begin to have gaps in your records? What happens when that ordering does not come out ascending - these are never guarantees hence you need the `ORDER BY` clause. — JonH, Dec 30 '11 at 16:47

JonH · Accepted Answer · 2011-12-30T15:56:32.003

Assume a table called Colors with fields ID, Color, and ColorIndex of types int, varchar, and int respectively. I also assume the OP means prev / after based on an ordering of the ID field in asc order.

You could do this without a cursor, and use a while loop...but it definately isn't set based:

DECLARE @MyID int
DECLARE @CurrentIndex int
DECLARE @CurrentColor varchar(50)
DECLARE @PreviousColor varchar(50)

SET @CurrentIndex = (SELECT 0)

SET @MyID = (SELECT TOP 1 ID FROM Colors ORDER BY ID ASC)
SET @CurrentColor = (SELECT '')
SET @PreviousColor = (SELECT Color FROM Colors WHERE ID = @MyID)

WHILE (@MyID IS NOT NULL)
 BEGIN
   IF (@CurrentColor <> @PreviousColor)
     BEGIN
        SET @PreviousColor = (SELECT Color FROM Colors WHERE ID = @MyID)
        SET @CurrentIndex = (SELECT @CurrentIndex + 1)
        UPDATE Colors SET ColorIndex = @CurrentIndex WHERE ID = @MyID
     END 
   ELSE
      BEGIN
        UPDATE Colors SET ColorIndex = @CurrentIndex WHERE ID = @MyID
        SET @PreviousColor = (SELECT Color FROM Colors WHERE ID = @MyID)
      END
 SET @MyID = (SELECT TOP 1 ID FROM Colors WHERE ID > @MyID ORDER BY ID ASC)
 SET @CurrentColor = (SELECT Color FROM Colors WHERE ID = @MyID)
 END

The result after execution:

enter image description here

Performance wasn't too shabby as long as ID and color are indexed. The plus side is it is a bit faster then using a regular old CURSOR and it's not as evil. Solution supports SQL 2000, 2005, and 2008 (being that you are using SQL 2000 which did not support CTEs).

Not guaranteed to work - `(SELECT TOP 1 ID FROM Colors WHERE ID > @MyID)` could select the last row during the first iteration, because you don't have an `ORDER BY` clause. — Damien_The_Unbeliever, Dec 30 '11 at 15:43
Damien - look at the top of my answer `SET @MyID = (SELECT TOP 1 ID FROM Colors ORDER BY ID ASC)` this will get the smallest value first, and being that it is integer, then the comparison operators, in this case `>`, are guaranteed to return the next value, so it is guaranteed to work. — JonH, Dec 30 '11 at 15:46
within your loop, when you try to move to the *next* `ID` value, you're *not* using an `ORDER BY` - so at the end of the first iteration, it could assign `@MyID` the highest `ID` value in the table, and skip all of the intervening rows. — Damien_The_Unbeliever, Dec 30 '11 at 15:49
Damien - You are right I overlooked that last line, fixed it. Thank you. — JonH, Dec 30 '11 at 15:50

score 2 · Answer 2 · edited Jan 18 '21 at 12:30

2

declare @ID int, 
        @MaxID int, 
        @NewIndex int, 
        @PrevCol varchar(50)

select @ID = min(ID),
       @MaxID = max(ID),
       @PrevCol = '',
       @NewIndex = 0       
from YourTable       
   
while @ID <= @MaxID
begin
  select @NewIndex = case when Colour = @PrevCol 
                       then @NewIndex 
                       else @NewIndex + 1 
                     end,
         @PrevCol = Colour
  from YourTable
  where ID = @ID
  
  update YourTable 
  set NewIndex = @NewIndex
  where ID = @ID
  
  set @ID = @ID + 1
end

https://data.stackexchange.com/stackoverflow/q/122958/

edited Jan 18 '21 at 12:30

Community

1
1

answered Dec 30 '11 at 15:57

Mikael Eriksson

136,425
22
210
281

This was fast with 5 records, but with i.e: 10000 records? Is that good enough? – crsuarezf Dec 30 '11 at 16:08
2

@ingcarlos - try it, see my answer about the indexes. The solution is the same as Mikael's, his is less wordy. – JonH Dec 30 '11 at 16:09
@JonH I'm also evaluating ur solution, both are good ones, however, both make use of (declare, set), and that's ok, But I'm evaluating a solution that doesn't have to store in a function or a store-procedure. – crsuarezf Dec 30 '11 at 16:14
1

@ingcarlos Tested on 10000 records and it took 2 seconds. On a laptop with 1.2 GHz Intel Duo processor. Is it good enough? You tell me. – Mikael Eriksson Dec 30 '11 at 16:19
1

@ingcarlos You don't *need* to put this in a stored procedure. You can send the entire thing as a query of it`s own. How you do it is dependent on what client framework you use but I'm sure it is possible. – Mikael Eriksson Dec 30 '11 at 16:20
@ingcarlos - Seems pretty fast for me, do you work for NASA? Ultimately what speed are you after? None of this has to be in a stored procedure or function, in fact my code is just in a query window. – JonH Dec 30 '11 at 16:23
@JonH, I would love to. I'm testing both solutions, they seem very well for my needs. – crsuarezf Dec 30 '11 at 16:40

score 1 · Answer 3 · answered Dec 30 '11 at 16:40

    select
        IDENTITY(int,1,1) as COUNTER
        ,c1.ID
    into
        #temp
    from
        CUSTOMERS c1
        left outer join (
                select
                    c1.ID, max(p.ID) as PRV_ID
                from
                    CUSTOMERS c1, 
                    (
                    select
                        ID
                    from
                        CUSTOMERS
                    ) p
                where
                    c1.ID > p.ID
                group by
                    c1.ID
                    ) k on k.ID = c1.ID
        left outer join CUSTOMERS p on p.ID = k.PRV_ID 
    where        
        ((c1.FAVOURITE_COLOUR < p.FAVOURITE_COLOUR)
         or
        (c1.FAVOURITE_COLOUR > p.FAVOURITE_COLOUR)
         or
         p.FAVOURITE_COLOUR is null)

    update
        CUSTOMERS
    set
        NEW_INDEX = i.COUNTER 
    --select *
    from    
        CUSTOMERS
        inner join (
            select 
                c1.ID, max(t.COUNTER) as COUNTER
            from
                CUSTOMERS c1, 
                (
                select
                    ID
                    ,COUNTER
                from
                    #temp
                ) t
            where
                c1.ID >= t.ID
            group by
                c1.ID
            ) i on i.ID = CUSTOMERS.ID

    drop table #temp

    select * from CUSTOMERS

SQL Server 2000 : generating and incrementing data from column conditionally without using CURSOR

3 Answers3