INSERT data on MySQL to specific position based on its value (similar to ORDER BY)

Question

I have a MySQL table with 3 columns for simplicity sake:

Datename     |Date    |Name
-------------+--------+-----
30/06/15test |30/06/15|test
30/06/15other|30/06/15|other

Note: Column Datename is unique

I would like all new data to be inserted into the table based on its value.

Example:

• If the Date is 29/06/15, it would be inserted at bottom of table.
• If the Date is 01/07/15, it would be inserted at the top of the table

Or preferably sorted by Datename column so it will sort by date then name.

Is this easily achievable, or do I need to just create a page that SELECT and ORDER BY?

Answer 1

A table is, by definition, an unordered bag of rows. There is no guarantee that if you say SELECT * FROM table you will get the rows back in the same order you inserted. Think about throwing a bunch of popsicle sticks on the ground while blindfolded; now take off the blindfold and tell me which one hit the floor last.

No ORDER BY is essentially telling the database you don't care about order.

If you do care about order, you need two things:

1. At least one column that can dictate order (an auto increment column, a date/time column populated with now(), or something you manually specify).

2. An actual ORDER BY expression on the outermost part of the query that presents the data (and don't forget to include a tie-breaker, if the first column might not be unique).

Answer 2

Check Codd's rules - there's no mention of "ordering". You can order by a PRIMARY KEY (in the SELECT statement) but you aren't forced to.

If you use ORDER BY in your SELECT for your INSERT and use MySQL's AUTO_INCREMENT to INSERT into your target table, then you should have the target table's PK be "ordered" by your criteria.

You should be aware that the SQL standard doesn't specify an ordering unless the SELECT query specifies it. So, you could do SELECT * FROM My_Table (no order by) and get different results from two queries in a row (however, it does appear to me that this is implicitly ordered by PK by the RDBMS).