0

Possible Duplicate:
select bottom rows in natural order

People imagine that i have this table :

persons

columns of the table are NAME and ID

and i insert this 

insert into persons values ('name','id');

insert into persons values ('John','1');

insert into persons values ('Jack','3');

insert into persons values ('Alice','2');

How can i select this information order by the insertion? My query would like :

NAME ID

name id

John 1

Jack 3

Alice 2

Without indexs (autoincrements), it's possible?

Community
  • 1
  • 1
unpix
  • 853
  • 2
  • 15
  • 31
  • You need to be storing that info (date-time-of-insertion, too) in the table. If you use MySQL, you could have a `TIMESTAMP` column that does that automatically. – ypercubeᵀᴹ Jun 05 '12 at 20:09
  • But is not possible select the data without timestamp and sequences? Because i'm working with 3 different types of DBMS (MySQL,Postgres and SQLserver) the postgres select * from tablename gives me the result that i want, but in MySQL and SQLServer not. – unpix Jun 05 '12 at 20:13
  • no. not possible. you can use default(getdate())? – sam yi Jun 05 '12 at 20:15
  • 3
    No, there is no guarantee that a `SELECT` statement without an `ORDER BY` will return the result in any specific order. What you see in Postgres in only coincidence. It may not happen the next time. Or when your table has 20K rows. Or when your query has a `WHERE`. – ypercubeᵀᴹ Jun 05 '12 at 20:15
  • MySQL, Postgres and SQLserver – unpix Jun 05 '12 at 20:24
  • 4
    @unpix there really is no way to *ensure* this without `AUTOINCREMENT` (MySQL), `IDENTITY` (MSSQL), `SEQUENCE` (PostgreSQL) ... or some time-based column that is set at insert. – swasheck Jun 05 '12 at 20:35
  • Thanks, i will modify my code ;) – unpix Jun 05 '12 at 20:39

5 Answers5

2

I'm pretty sure its not. From my knowldege sql data order is not sequetional with respect to insertion. The only idea I have is along with each insertion have a timestamp and sort by that time stamp

Florin Stingaciu
  • 8,085
  • 2
  • 24
  • 45
2

This is not possible without adding a column or table containing a timestamp. You could add a timestamp column or create another table containing IDs and a timestamp and insert in to that at the same time.

smcg
  • 3,195
  • 2
  • 30
  • 40
  • 2
    Technically it could be anything sequential (e.g., a SEQUENCE value), it wouldn't necessarily have to be a timestamp. But you are correct that there is no way to guarantee this result in SQL. – Todd Gibson Jun 05 '12 at 20:13
2

You cannot have any assumptions about how the DBMS will store data and retrieve them without specifying order by clause. I.e. PostgreSQL uses MVCC and if you update any row, physically a new copy of a row will be created at the end of a table datafile. Using a plain select causes pg to use sequence scan scenario - it means that the last updated row will be returned as the last one.

ManieQ
  • 330
  • 2
  • 8
1

I have to agree with the other answers, Without a specific field/column todo this... well its a unreliable way... While i have not actually ever had a table without an index before i think..

you will need something to index it by, You can go with many other approaches and methods... For example, you use some form of concat/join of strings and then split/separate the query results later.

--EDIT-- For what reason do you wish not to use these methods? time/autoinc

Angry 84
  • 2,935
  • 1
  • 25
  • 24
0

Without storing some sort of order information during insert, the database does not automatically keep track of every record ever inserted and their order (this is probably a good thing ;) ). Autoincrement cannot be avoided... even with timestamp, they can hold same value.

sam yi
  • 4,806
  • 1
  • 29
  • 40