Return Rows in the Exact Order They Were Inserted

Return rows in the exact order they were inserted

As others have already written, you will not be able to get the rows out of the link table in the order they were inserted.

If there is some sort of internal ordering of the rows in one or both of the tables that this link table is joining, then you can use that to try to figure out when the link table rows have been created. Basically, they cannot have been created BEFORE both of the rows containing the PK:s have been created.

But on the other hand you will not be able to find out how long after they have been created.

If you have decent backups, you could try to restore one or a few backups of varying age and then try to see if those backups also contains this strange behaviour. It could give you at least some clue about when the strangeness has started.

But the bottom line is that using just a select, there is now way to get the row out of a table like this in the order they were inserted.

SQL Server Select in Same Order Inserted

As has been asked and answered before on Stack Overflow, you cannot get the records out in the same order in which they were inserted. More specifically, the SQL Server engine makes no such gurantee. The reason for this is that your database engine needs to be free to store those inserted records how ever it wants to be efficient and optimal.

If you want to maintain the order during insertion, you could create an autoincrement column. This column would keep count of the insertion order for cast members, and you would be able to query out the original order.

How can I determine the actual database row insertion order?

DATE datatypes only go to seconds, whereas TIMESTAMP goes to milliseconds. Would that address the problem?

According to Oracle's docs:

TIMESTAMP: Year, month, and day values
of date, as well as hour, minute, and
second values of time, where
fractional_seconds_precision is the
number of digits in the fractional
part of the SECOND datetime field.
Accepted values of
fractional_seconds_precision are 0 to
9. The default is 6. The default format is determined explicitly by the
NLS_DATE_FORMAT parameter or
implicitly by the NLS_TERRITORY
parameter. The sizes varies from 7 to
11 bytes, depending on the precision.
This datatype contains the datetime
fields YEAR, MONTH, DAY, HOUR, MINUTE,
and SECOND. It contains fractional
seconds but does not have a time zone.

Whereas date does not:

DATE: Valid date range from January 1,
4712 BC to December 31, 9999 AD. The
default format is determined
explicitly by the NLS_DATE_FORMAT
parameter or implicitly by the
NLS_TERRITORY parameter. The size is
fixed at 7 bytes. This datatype
contains the datetime fields YEAR,
MONTH, DAY, HOUR, MINUTE, and SECOND.
It does not have fractional seconds or
a time zone.

Of course, having said that, I am not sure why it matters when the records were written, but that is a way that might solve your problem.

How to ORDER BY and INSERT to TABLE when loading data from JSON

Actually, it inserts ordered but you should also select it with order by. Because select not guaranteed to return sorted data as inserted.

SELECT * FROM @V_CALLSTBL ORDER BY CreatedDate ASC;

and you can add an identity column to the table for seeing this.

DECLARE @IN_DATESJSON NVARCHAR(MAX) = N'[{"CreatedDate":"2018-10-10T09:07:29Z"},{"CreatedDate":"2018-10-09T09:07:29Z"},{"CreatedDate":"2018-10-08T07:07:08Z"}]';
DECLARE @V_CALLSTBL AS TABLE (ID INT IDENTITY(1,1), CreatedDate DATETIME);
IF (ISJSON(@IN_DATESJSON) = 1)
    BEGIN  
        INSERT INTO @V_CALLSTBL
        SELECT *
        FROM OPENJSON (@IN_DATESJSON)
            WITH (CreatedDate DATETIME2)
        ORDER BY CreatedDate ASC -- THIS DOESN'T WORK*
    END
SELECT * FROM @V_CALLSTBL ORDER BY CreatedDate ASC;

In SQL, does the LIMIT returns the row which is inserted the last in chronological order?

You're misunderstanding how SQL works. You're thinking row-by-row which is wrong. SQL does not "traverse rows" as per your concern; it operates on data as "sets".

Others have pointed out that relational database cannot be assumed to have any particular ordering, so you must use ORDER BY to explicitly specify ordering.

However (not mentioned yet is that), in order to ensure it performs efficiently, you need to create an appropriate index.

Whether you have an index or not, the correct query is:

SELECT  <cols>
FROM    <table>
ORDER BY <sort-cols> [DESC] LIMIT <no-rows>

Note that if you don't have an index the database will load all data and probably sort in memory to find the TOP n.

If you do have the appropriate index, the database will use the best index available to retrieve the TOP n rows as efficiently as possible.

---

Note that the sqllite documentation is very clear on the matter. The section on ORDER BY explains that ordering is undefined. And nothing in the section on LIMIT contradicts this (it simply constrains the number of rows returned).

If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined.

This behaviour is also consistent with the ANSI standard and all major SQL implementations. Note that any database vendor that guaranteed any kind of ordering would have to sacrifice performance to the detriment of queries trying to retrieve data but not caring about order. (Not good for business.)

---

As a side note, flawed assumptions about ordering is an easy mistake to make (similar to flawed assumptions about uninitialised local variables).

RDBMS implementations are very likely to make ordering appear consistent. They follow a certain algorithm for adding data, a certain algorithm for retrieving data. And as a result, their operations are highly repeatable (it's what we love (and hate) about computers). So things repeatably look the same.

Theoretical examples:

• Inserting a row results in the row being added to the next available free space. So data appears sequential. But an update would have to move the row to a new location if it no longer fits.
• The DB engine might retrieve data sequentially from clustered index pages and seem to use clustered index as the 'natural ordering' ... until one day a page-split puts one of the pages in a different location. * Or a new version of the DMBS might cache certain data for performance, and suddenly order changes.

Real-world example:

• The MS SQL Server 6.5 implementation of GROUP BY had the side-effect of also sorting by the group-by columns. When MS (in version 7 or 2000) implemented some performance improvements, GROUP BY would by default, return data in a hashed order. Many people blamed MS for breaking their queries when in fact they had made false assumptions and failed to ORDER BY their results as needed.

This is why the only guarantee of a specific ordering is to use the ORDER BY clause.

How does insertion order affect output inserted value?

There is no way to know the order.

If you look at this page from MSDN (http://msdn.microsoft.com/en-us/library/ms177564.aspx) you will find the following statement:

"There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond."

How to maintain the sort at insert-select scripts?

it would be reasonable to expect that the very first inserted record would be first

I don't think so. You used ORDER BY SortByFilter = 'first' which returns 0 for all values except 'first', followed by 1 for 'first'. The value 1 sorts after the value 0, so the entry 'first' ends up being last. The other values end up sorting more or less randomly.

Demo:

mysql> create table mytable (SortByFilter varchar(64));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytable values ('first'), ('first-ASC'), 
  ('last'), ('none'), ('StatTeacher.IsActive DESC');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select SortByFilter='first', SortByFilter from mytable 
  order by SortByFilter = 'first';
+----------------------+---------------------------+
| SortByFilter='first' | SortByFilter              |
+----------------------+---------------------------+
|                    0 | first-ASC                 |
|                    0 | last                      |
|                    0 | none                      |
|                    0 | StatTeacher.IsActive DESC |
|                    1 | first                     |
+----------------------+---------------------------+

I suggest do not rely on automatic sorting. Be specific about the sort order of every value. Here's one way to do it:

mysql> select field(SortByFilter, 'first', 'first-ASC',
  'none', 'StatTeacher.IsActive DESC', 'last') AS SortOrder, 
  SortByFilter 
  from mytable order by SortOrder;
+-----------+---------------------------+
| SortOrder | SortByFilter              |
+-----------+---------------------------+
|         1 | first                     |
|         2 | first-ASC                 |
|         3 | none                      |
|         4 | StatTeacher.IsActive DESC |
|         5 | last                      |
+-----------+---------------------------+

---

Related Topics