Order of records isn't maintained with ORDER BY

Help
1

(Ported from memsql-public-chat Slack channel)

athan [10:45 AM]
Hi I have a problem with a query can anyone help me ?

SELECT
   from_station_id AS station_id,
   from_station_name AS station_name
FROM TripFlat
GROUP BY
   from_station_id,
   from_station_name
ORDER BY
   from_station_id ASC

When I create a view with a SELECT ... ORDER BY the order is not maintained
When I insert in a table with INSERT INTO table SELECT ... ORDER BY the order of records in the table is not maintained why ?

athan [11:14 AM]
My problem is that the order of records, i.e. order by from_station_id is not maintained when I select * from VW, or select * from Station
Why is that behavior, am I missing something fundamental here ? In another SQL DBMS order is maintained.
Order of records is not maintained in a View or with an INSERT

CREATE VIEW VW AS
SELECT 
  from_station_id AS station_id, 
  from_station_name AS station_name
FROM TripFlat

jack [11:32 AM]
@athan if you want order of results, you must use order by in your query (order by in the view or in the insert doesn’t matter). if you don’t have an order by clause, the ordering is implementation-dependent, which is why some database do give ordered results. (e.g. see Order by - Wikipedia) (edited)

athan [11:59 AM]
@jack thank you for your prompt reply, I take your point about implementation-dependent ordering. I am comparing results with clickhouse columnar database if you specify that you want a MergetTree Table with an index (order by) then it also orders the records according to the index when you retrieve them with a select * from table, i.e. you don’t need to specify again order by

Now I suppose one can do the same here by defining an index either on a row oriented table or a columnar one, although you will have to make explicit the order by in select clause

Is that right ?

jack [12:02 PM]
yes, that’s right. the index will define the physical layout, so that the order by can be done efficiently, but you still need to specify order by to ensure ordering

athan [12:02 PM]
Perfect thanks for clarifying this

So the the index in memsql is hidden as it is normally the case with other DBMS too.

I have to go now, keep in touch the project I am working on is http://healis.eu/triadb

2

Replied by Jack already in the body of message.