(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 https://en.wikipedia.org/wiki/Order_by) (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