[R-sig-DB] RODBC sqlSave appends rows from data frame in reverse order

[Prof Brian Ripley]

On 20/11/2013 16:34, Enrico Schumann wrote:
> On Wed, 20 Nov 2013, "Chris" <cs using incentive-investing.com> writes:
>
>> Hi,
>>
>> I append rows to a table in MySQL with sqlSave from RODBC package. The data
>> frame that is saved is ordered by date ascending (first column) as is the
>> table in the DB. When I send the whole data frame at once, the data frame is
>> appended in reverse order. That means the row with the newest date in the
>> data frame is the first row appended to the table, while the row with oldest
>> date will be last row in the table. OK, I thought that "append" just orders
>> by date descending and that is why the reverse ordering happens. But when I
>> send each row of the data frame using a for-loop the result is the same: The
>> first row send is the last row in the table, the second row send, is the
>> second last row in the table.
>>
>> My solution is to run the for loop from last to first row of the data frame
>> but I am looking for an explanation why append works like this and if it's
>> possible to change this in some setting.
>>
>
> In MySQL, you never have a guarantee in which order the rows will be
> retrieved unless you specify it (eg, "SELECT * FROM table ORDER BY
> column;").

Nor any other RDBMS: as the RODBC manual does say (on p.6).

>
> [...]
>
>>
>> Thank you
>>
>> Chris
>>
>


-- 
Brian D. Ripley,                  ripley using stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595