Table ordering help

[Ruslan Zasukhin]

On 2/7/09 7:14 PM, "Antuan Johnson" <amjohnson09 at gmail.com> wrote:

Hi Antuan, 

> I have to update the column(Chart_Position) based on the order of
> column(sales) each week. So I guess dream code
> 
> would look like Update T1 Set Chart_Position = Temprecid ORDER BY Sales DESC
> 
> I had an alternative way before Valentina but it doesn't seem possible now
> because ORDER BY is not allowed to be used in INSERT statements, and I'm not
> knowledgeable of cursors or procedures so any help is appreciated. Heres the
> code I developed before.
> 
> BEGIN TRANSACTION;
> CREATE TEMPORARY TABLE chart_backup(Act String,Sales Long,Chart_Position
> BYTE);
> INSERT INTO chart_backup SELECT Discs.Act, Discs.Sales, Discs.Chart_Position
> FROM Discs ORDER BY Sales Desc;
> CREATE TABLE newchart(Act String,Sales Long,Chart_Position BYTE;
> INSERT INTO newchart(Act,Sales,chart_backup) SELECT Act,Sales,Chart_Position
> FROM chart_backup
> COMMIT;
> update Discs Set P=(Select recid from  chart_backup WHERE
> chart_backup.Sales=Discs.Sales)



Well, if you not fan of SQL can be API way:

    curs = db.SqlSelect(
        "SELECT Chart_Position FROM Disc ORDER BY Sales Desc" )

    if curs.RecordCount = 0 then
        return
    end if

    do    
        curs.ByteField("Chart_Position").value = curs.Position
        curs.UpdateRecord()

    while curs.NextRecord()


If you going to work with REMOTE VSERVER, then I think the same code
Can be made as Stored Procedure to be called once on remote server without
network overhead.




-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]