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

[Chris]

Great, thank you all.
I will always add "order by" when fetching data into da data frame and
assume the rows in MySQL might not always be in right order

-----Ursprüngliche Nachricht-----
Von: Prof Brian Ripley [mailto:ripley using stats.ox.ac.uk] 
Gesendet: Mittwoch, 20. November 2013 18:19
An: Enrico Schumann; Chris
Cc: r-sig-db using r-project.org
Betreff: Re: [R-sig-DB] RODBC sqlSave appends rows from data frame in
reverse order

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