SQL question

SQL question

Post by Robert Keule » Sun, 28 Nov 1999 04:00:00



Hiya

I am trying to accomplish something using SQL, but am too novice at it
to come up with a working solution. I wonder if someone has any tips for
me. Here we go...

I have, in an SQL server 6.5, one table of some substance (1.5 milion
records). It is a flat table with no relations or triggers or whatever.

What I want to do is make an aggregate query that groups certain rows.
No problem, I can do that. Now, what I want to do next is the following:
I want to analyse two subsequent rows at a time, placing one field of
each record into a new table. It should look something like this:

newtable.field1=resultset(row 1).field X, newtable.field2=resultset(row
2).field X
newtable.field1=resultset(row 2).field X, newtable.field2=resultset(row
3).field X
newtable.field1=resultset(row 3).field X, newtable.field2=resultset(row
4).field X
etc.

I hope this description is a bit clear. Is there a way of accomplishing
this using just ANSI SQL? Or will I have to resort to VB or similar
things to process each row?

Hope someone can help me with this

Robert

PS if anybody could send hinst and tips through email, that would be
great; my usenet time is very limited. PLease remove the obvious from
the email addy

 
 
 

SQL question

Post by Denis Jedi » Mon, 29 Nov 1999 04:00:00



> I hope this description is a bit clear. Is there a way of accomplishing
> this using just ANSI SQL? Or will I have to resort to VB or similar
> things to process each row?

For me it's not very clear.
For me it seems that you want to perform recordset-based operations
rather than set-based. If it is true, you should consider the use of
TSQL-Cursors. This way it is still significantly faster than using
front-end (VB) based manipulation.

Quote:> Robert

Denis Jedig
--
Hi! I'm a .signature virus! Copy me into your ~/.signature to help me
spread!

 
 
 

SQL question

Post by Mike Irwin [CTech » Mon, 29 Nov 1999 04:00:00


I'd suggest that you write a stored procedure to create a results table
with as much information from the first table as it can, and then,
still within the SP, use loops to revisit the source table, pulling the
desired information.

I would also note that you should not rely on a sequential relationship
between records such as you describe (record n, n+1, n+2 ...) because
of the ability of another user to insert records into your process
during processing, thus corrupting it, or afterwards, making its
results unreproducable.

--
Mike Irwin                    It's not because things are hard that we
[Volunteer CTech Sysop,       don't dare them; it's because we don't
not a Corel employee]         dare them that they are hard - Seneca.