How to write an SQL query to get records based on the order in which they were entered? (No date of birth)


This question already has an answer here:

  • Is there a “Default Order By Column” in SQL Server? 2 answers
  • How to SELECT the last 10 rows of an SQL table which has no ID field? 13 answers

I need to write a SQL query that returns records in the order in which they were entered in to a table.

I can't add a column to the table or change the table in any way. I can insert into and select from the table.

Say I execute three insert queries like

  insert into x values(a,1);
  insert into x values(d,4);
  insert into x values(u,42);

when I select from the table x I need to get the records in this order.

  • a 1
  • d 4
  • u 42

The table has only two columns, both have nothing to do with date.

You can't do it without changing the table in some way.

When you select data from a table the order in which it is returned is non-deterministic on all the sql database engines I know and certainly in MSSQL server 2000+. To get the rows in a defined order you must include an ORDER BY clause and there is nothing you can specify to give the desired order.

Since you cannot change the schema, then this is game over.

Okay, (almost) nothing is impossible. You could periodically analyse the physical database file for changes and decode those into the information you require but, this would likely fail when multiple rows were inserted by one transaction.

I doubt you have the access or the inclination to do that.