Table DDL:

create table test
(
inId int primary key identity,
inFK int not null,
inSeq int not null,
dtDate datetime
)

Data Insert:
insert into test
select 1,1, getdate()

WAITFOR DELAY '000:00:01'

insert into test
select 1,1, getdate()

WAITFOR DELAY '000:00:01'

insert into test
select 1,1, getdate()

WAITFOR DELAY '000:00:01'

insert into test
select 2,1, getdate()

WAITFOR DELAY '000:00:01'

insert into test
select 2,1, getdate()

WAITFOR DELAY '000:00:01'

insert into test
select 3,1, getdate()

WAITFOR DELAY '000:00:01'

insert into test
select 4,1, getdate()


If we select on this table:
inId inFK inSeq dtDate

----------- ----------- -----------
------------------------------------------------------
1 1 1 2005-02-01 12:54:40.967
2 1 1 2005-02-01 12:54:41.967
3 1 1 2005-02-01 12:54:42.967
4 2 1 2005-02-01 12:54:43.967
5 2 1 2005-02-01 12:54:44.967
6 3 1 2005-02-01 12:54:45.983
7 4 1 2005-02-01 12:54:47.077

(7 row(s) affected)


Problem:
What I would like to do (using SQL and not a cursor) is to update the
value of inSeq to its ordinal position, this will be based on the # of
occurences of inFK. For Example, I would like to run a sql statement
that would transform the data to:

update test
set inSEQ = (some sql)

select * from test - This would then produce:

inId inFK inSeq dtDate

----------- ----------- -----------
------------------------------------------------------
1 1 1 2005-02-01 12:54:40.967
2 1 2 2005-02-01 12:54:41.967
3 1 3 2005-02-01 12:54:42.967
4 2 1 2005-02-01 12:54:43.967
5 2 2 2005-02-01 12:54:44.967
6 3 1 2005-02-01 12:54:45.983
7 4 1 2005-02-01 12:54:47.077
(7 row(s) affected)


Any help would be greatly appreciated.

TFD

Hi

The following solution will work if the inserted records (with
same inFK value ) are in sequential order only.

update test set inSeq = (inid - (select min(inid) from test b where
a.infk = b.infk ) + 1) from test a

Thanks
Ssk

If the Records are
LineVoltageHalogen wrote:
> Table DDL:
>
> create table test
> (
> inId int primary key identity,
> inFK int not null,
> inSeq int not null,
> dtDate datetime
> )
>
> Data Insert:
> insert into test
> select 1,1, getdate()
>
> WAITFOR DELAY '000:00:01'
>
> insert into test
> select 1,1, getdate()
>
> WAITFOR DELAY '000:00:01'
>
> insert into test
> select 1,1, getdate()
>
> WAITFOR DELAY '000:00:01'
>
> insert into test
> select 2,1, getdate()
>
> WAITFOR DELAY '000:00:01'
>
> insert into test
> select 2,1, getdate()
>
> WAITFOR DELAY '000:00:01'
>
> insert into test
> select 3,1, getdate()
>
> WAITFOR DELAY '000:00:01'
>
> insert into test
> select 4,1, getdate()
>
>
> If we select on this table:
> inId inFK inSeq dtDate
>
> ----------- ----------- -----------
> ------------------------------------------------------
> 1 1 1 2005-02-01 12:54:40.967
> 2 1 1 2005-02-01 12:54:41.967
> 3 1 1 2005-02-01 12:54:42.967
> 4 2 1 2005-02-01 12:54:43.967
> 5 2 1 2005-02-01 12:54:44.967
> 6 3 1 2005-02-01 12:54:45.983
> 7 4 1 2005-02-01 12:54:47.077
>
> (7 row(s) affected)
>
>
> Problem:
> What I would like to do (using SQL and not a cursor) is to update the
> value of inSeq to its ordinal position, this will be based on the #
of
> occurences of inFK. For Example, I would like to run a sql
statement
> that would transform the data to:
>
> update test
> set inSEQ = (some sql)
>
> select * from test - This would then produce:
>
> inId inFK inSeq dtDate
>
> ----------- ----------- -----------
> ------------------------------------------------------
> 1 1 1 2005-02-01 12:54:40.967
> 2 1 2 2005-02-01 12:54:41.967
> 3 1 3 2005-02-01 12:54:42.967
> 4 2 1 2005-02-01 12:54:43.967
> 5 2 2 2005-02-01 12:54:44.967
> 6 3 1 2005-02-01 12:54:45.983
> 7 4 1 2005-02-01 12:54:47.077
> (7 row(s) affected)
>
>
> Any help would be greatly appreciated.
>
> TFD

I can probably control this by creating a clusterd primary key on inId,
inFk, and sdDate, do you concur?

TFD

No. A clustered index is irrelevant. It has no impact on the standard,
documented behaviour of an UPDATE statement. The results of certain unusual
and ambiguous UPDATE statements which are undefined in the documentation may
be affected by indexes but the behaviour of those remains the same in each
case: the result is undefined and therefore unreliable.

If inseq is based purely on the values of infk and dtdate then there is no
obvious need to put the column in the table at all. Derive the sequence when
you query the table or add the numbering at client-side:

SELECT T1.inid, T1.infk,
COUNT(*) AS inseq, T1.dtdate
FROM Test AS T1
JOIN Test AS T2
ON T1.infk = T2.infk
AND T1.dtdate >= T2.dtdate
GROUP BY T1.inid, T1.infk, T1.dtdate

--
David Portas
SQL Server MVP
--

On 1 Feb 2005 09:58:01 -0800, LineVoltageHalogen wrote:

(snip)
>Problem:
>What I would like to do (using SQL and not a cursor) is to update the
>value of inSeq to its ordinal position, this will be based on the # of
>occurences of inFK. For Example, I would like to run a sql statement
>that would transform the data to:
(snip)

Hi TFD,

Thanks for providing the create table and insert statements and the
expected output to clarify your need.

The following update statement will set the inSeq values as requested:

UPDATE test
SET inSeq = (SELECT COUNT(*)
FROM test AS t
WHERE t.inFK = test.inFK
AND t.dtDate <= test.dtDate)

By the way - using datatype-prefixes to column names is really not a
recommended practice. Would you really want to go over all your code if
the datatype of one of your columns has to be changed?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

David, thank you for your response. Doing stuff client side is not an
option for me, the example I put forth is just the gist of what I need
to do. I am actually implementing an ETL for a BI tool and this
example demonstrates the nature of the real problem I am solving. So,
an update statement is required, I also don't have the luxury of
knowing how many inFk,inSeq pairs will be coming in until after the
load completes. Can your query be modified to accomodate an update?
TFD

Hugo, if I understand correctly this query does not depend on the
physical order of the data on disk?

SQL statements never depend on the physical order of the data on disk.
That's a basic principle of an RDBMS.

Why do you ask?

--
David Portas
SQL Server MVP
--

I just want to understand, that is all. I was thinking that if they
had been sorted when I kicked off the update that SQL would just start
with the first record and then rip through them all from begining to
end. That would seem like the most natural action on SQL's part, how
else would it decide on the order in which is was going to execute the
update? Let's assume we have a simple query such as:

update table
set row = 'new value'

Wouldn't SQL just go to the head of the set and begin updating until it
reaches the end?

L

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> If inseq is based purely on the values of infk and dtdate then there is
> no obvious need to put the column in the table at all. Derive the
> sequence when you query the table or add the numbering at client-side:
>
> SELECT T1.inid, T1.infk,
> COUNT(*) AS inseq, T1.dtdate
> FROM Test AS T1
> JOIN Test AS T2
> ON T1.infk = T2.infk
> AND T1.dtdate >= T2.dtdate
> GROUP BY T1.inid, T1.infk, T1.dtdate

The performance of this type of query is not always that fantastic, so
if you need the row numbering often, it may indeed be a good idea to
compute it once.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp