Is it possible, that "SELECT *" returns records, where columns are
ordered in a different order, than the order they were
positioned/specified when table was created?

For example, lets assume that table T contains columns c1,c2,..c10.

Can this be possible, that the * (asterisk) shows the columns in a
different orderm the c1 and c10 changed their position for example:

query:

"SELECT * FROM T"

results:

c10, c2, c3, .., c9, c1.

I think you anderstood my question now.

The sql-standard doesn't tell about it nothing, but i have heard that
"selesct *" can produce columns in a random order in some situations.

Notice, that i'm not talking about "ORDER"-operator. I have heard that if one make a table with plenty of columns and adds plenty of indexes and constraints to the columns, then "select *" really will produce mistery column order.


Thank you for answering,
Regards.

: Is it possible, that "SELECT *" returns records, where columns are
: ordered in a different order, than the order they were
: positioned/specified when table was created?
:
: For example, lets assume that table T contains columns c1,c2,..c10.
:
: Can this be possible, that the * (asterisk) shows the columns in a
: different orderm the c1 and c10 changed their position for example:
:
: query:
:
: "SELECT * FROM T"
:
: results:
:
: c10, c2, c3, .., c9, c1.
:
: I think you anderstood my question now.
:
: The sql-standard doesn't tell about it nothing, but i have heard that
: "selesct *" can produce columns in a random order in some situations.
:
: Notice, that i'm not talking about "ORDER"-operator. I have heard that if one make a table with plenty of columns and adds plenty of indexes and constraints to the columns, then "select *" really will produce mistery column order.
:
:
: Thank you for answering,
: Regards.
:
Hi,
Yes, this can sometimes happen, but USUALLY the first column (0) is your Primary Key. The best way to ensure that you get them in a specific order is to name them in the SELECT:
EG _ "SELECT T.c1, T.c2, T.c3, ...... FROM T"



In a World full of blind men the one-eyed man is King

: : Is it possible, that "SELECT *" returns records, where columns are
: : ordered in a different order, than the order they were
: : positioned/specified when table was created?
: :
: : For example, lets assume that table T contains columns c1,c2,..c10.
: :
: : Can this be possible, that the * (asterisk) shows the columns in a
: : different orderm the c1 and c10 changed their position for example:
: :
: : query:
: :
: : "SELECT * FROM T"
: :
: : results:
: :
: : c10, c2, c3, .., c9, c1.
: :
: : I think you anderstood my question now.
: :
: : The sql-standard doesn't tell about it nothing, but i have heard that
: : "selesct *" can produce columns in a random order in some situations.
: :
: : Notice, that i'm not talking about "ORDER"-operator. I have heard that if one make a table with plenty of columns and adds plenty of indexes and constraints to the columns, then "select *" really will produce mistery column order.
: :
: :
: : Thank you for answering,
: : Regards.
: :
: Hi,
: Yes, this can sometimes happen, but USUALLY the first column (0) is your Primary Key. The best way to ensure that you get them in a specific order is to name them in the SELECT:
: EG _ "SELECT T.c1, T.c2, T.c3, ...... FROM T"
:
:
:
: In a World full of blind men the one-eyed man is King
:
:




"Yes, this can sometimes happen"

Can you specify, in which conditions this can happen?
What should i do, to see this random-ordering? I don't understand what the primary key column position can affect to the problem.

Thank you for answering.

: : : Is it possible, that "SELECT *" returns records, where columns are
: : : ordered in a different order, than the order they were
: : : positioned/specified when table was created?
: : :
: : : For example, lets assume that table T contains columns c1,c2,..c10.
: : :
: : : Can this be possible, that the * (asterisk) shows the columns in a
: : : different orderm the c1 and c10 changed their position for example:
: : :
: : : query:
: : :
: : : "SELECT * FROM T"
: : :
: : : results:
: : :
: : : c10, c2, c3, .., c9, c1.
: : :
: : : I think you anderstood my question now.
: : :
: : : The sql-standard doesn't tell about it nothing, but i have heard that
: : : "selesct *" can produce columns in a random order in some situations.
: : :
: : : Notice, that i'm not talking about "ORDER"-operator. I have heard that if one make a table with plenty of columns and adds plenty of indexes and constraints to the columns, then "select *" really will produce mistery column order.
: : :
: : :
: : : Thank you for answering,
: : : Regards.
: : :
: : Hi,
: : Yes, this can sometimes happen, but USUALLY the first column (0) is your Primary Key. The best way to ensure that you get them in a specific order is to name them in the SELECT:
: : EG _ "SELECT T.c1, T.c2, T.c3, ...... FROM T"
: :
: :
: :
: : In a World full of blind men the one-eyed man is King
: :
: :
:
:
:
:
: "Yes, this can sometimes happen"
:
: Can you specify, in which conditions this can happen?
: What should i do, to see this random-ordering? I don't understand what the primary key column position can affect to the problem.
:
: Thank you for answering.
:
I presume that you are querying on either a MSAccess or SQL_Server database, you don't actually say. But these are "Relational" Databases, not sequential. So unless you tell the database engine how you want it to fetch things it will optimise the query and fetch it in the best possible way for that particular time. This will depend very much on what else the database engine is doing at the time, so you could (in theory) run the same query 100 times over and get the results in 100 different ways. As far as the Primary Key is concerned, by virtue of what it is the database engine will be using this to keep track of that row, and will therefore (usually) put it first.
Hope that helps you to understand what is going on.

In a World full of blind men the one-eyed man is King

: : : : Is it possible, that "SELECT *" returns records, where columns are
: : : : ordered in a different order, than the order they were
: : : : positioned/specified when table was created?
: : : :
: : : : For example, lets assume that table T contains columns c1,c2,..c10.
: : : :
: : : : Can this be possible, that the * (asterisk) shows the columns in a
: : : : different orderm the c1 and c10 changed their position for example:
: : : :
: : : : query:
: : : :
: : : : "SELECT * FROM T"
: : : :
: : : : results:
: : : :
: : : : c10, c2, c3, .., c9, c1.
: : : :
: : : : I think you anderstood my question now.
: : : :
: : : : The sql-standard doesn't tell about it nothing, but i have heard that
: : : : "selesct *" can produce columns in a random order in some situations.
: : : :
: : : : Notice, that i'm not talking about "ORDER"-operator. I have heard that if one make a table with plenty of columns and adds plenty of indexes and constraints to the columns, then "select *" really will produce mistery column order.
: : : :
: : : :
: : : : Thank you for answering,
: : : : Regards.
: : : :
: : : Hi,
: : : Yes, this can sometimes happen, but USUALLY the first column (0) is your Primary Key. The best way to ensure that you get them in a specific order is to name them in the SELECT:
: : : EG _ "SELECT T.c1, T.c2, T.c3, ...... FROM T"
: : :
: : :
: : :
: : : In a World full of blind men the one-eyed man is King
: : :
: : :
: :
: :
: :
: :
: : "Yes, this can sometimes happen"
: :
: : Can you specify, in which conditions this can happen?
: : What should i do, to see this random-ordering? I don't understand what the primary key column position can affect to the problem.
: :
: : Thank you for answering.
: :
: I presume that you are querying on either a MSAccess or SQL_Server database, you don't actually say. But these are "Relational" Databases, not sequential. So unless you tell the database engine how you want it to fetch things it will optimise the query and fetch it in the best possible way for that particular time. This will depend very much on what else the database engine is doing at the time, so you could (in theory) run the same query 100 times over and get the results in 100 different ways. As far as the Primary Key is concerned, by virtue of what it is the database engine will be using this to keep track of that row, and will therefore (usually) put it first.
: Hope that helps you to understand what is going on.
:
: In a World full of blind men the one-eyed man is King
:
:


I don't understand what you said.

"so you could (in theory) run the same query 100 times over and get the results in 100 different ways. "

Yes, the optimization can do things, but always, the "select *" should return the columns int the order they were specified in the "create table"-clause. The optimization affects only how the data is taken on the hard disk, the optimization shouldn't be able to change the column positions on the output recordset.


"As far as the Primary Key is concerned, by virtue of what it is the database engine will be using this to keep track of that row, and will therefore (usually) put it first.
Hope that helps you to understand what is going on. "

The primary and other stuff should only affect in which order the DATA records will be ordered. If table T was declared so that columns were specified in the order c1, c2, ..c10, then select * should have to return the columns in the same order, c1 for first position in the recordset and c10 as the last column i nthe recordset, it caanot produse such output:

"SELECT * FROM T"
c10, c2, c3, .., c9, c1.

I don't understand your explanations, maybe you didn't understand my question. I repeat: i don't ask about recors/row ordering, i am asking about columns position/order in the output.

I have heard that select * can produce output with mistery column positions/orders. The data retrieved and the data's order does not interest me.

Do you copy?

This message was edited by Geoff-S at 2004-11-25 7:36:51

---

: : : : : Is it possible, that "SELECT *" returns records, where columns are
: : : : : ordered in a different order, than the order they were
: : : : : positioned/specified when table was created?
: : : : :
: : : : : For example, lets assume that table T contains columns c1,c2,..c10.
: : : : :
: : : : : Can this be possible, that the * (asterisk) shows the columns in a
: : : : : different orderm the c1 and c10 changed their position for example:
: : : : :
: : : : : query:
: : : : :
: : : : : "SELECT * FROM T"
: : : : :
: : : : : results:
: : : : :
: : : : : c10, c2, c3, .., c9, c1.
: : : : :
: : : : : I think you anderstood my question now.
: : : : :
: : : : : The sql-standard doesn't tell about it nothing, but i have heard that
: : : : : "selesct *" can produce columns in a random order in some situations.
: : : : :
: : : : : Notice, that i'm not talking about "ORDER"-operator. I have heard that if one make a table with plenty of columns and adds plenty of indexes and constraints to the columns, then "select *" really will produce mistery column order.
: : : : :
: : : : :
: : : : : Thank you for answering,
: : : : : Regards.
: : : : :
: : : : Hi,
: : : : Yes, this can sometimes happen, but USUALLY the first column (0) is your Primary Key. The best way to ensure that you get them in a specific order is to name them in the SELECT:
: : : : EG _ "SELECT T.c1, T.c2, T.c3, ...... FROM T"
: : : :
: : : :
: : : :
: : : : In a World full of blind men the one-eyed man is King
: : : :
: : : :
: : :
: : :
: : :
: : :
: : : "Yes, this can sometimes happen"
: : :
: : : Can you specify, in which conditions this can happen?
: : : What should i do, to see this random-ordering? I don't understand what the primary key column position can affect to the problem.
: : :
: : : Thank you for answering.
: : :
: : I presume that you are querying on either a MSAccess or SQL_Server database, you don't actually say. But these are "Relational" Databases, not sequential. So unless you tell the database engine how you want it to fetch things it will optimise the query and fetch it in the best possible way for that particular time. This will depend very much on what else the database engine is doing at the time, so you could (in theory) run the same query 100 times over and get the results in 100 different ways. As far as the Primary Key is concerned, by virtue of what it is the database engine will be using this to keep track of that row, and will therefore (usually) put it first.
: : Hope that helps you to understand what is going on.
: :
: : In a World full of blind men the one-eyed man is King
: :
: :
:
:
: I don't understand what you said.
:
: "so you could (in theory) run the same query 100 times over and get the results in 100 different ways. "
:
: Yes, the optimization can do things, but always, the "select *" should return the columns int the order they were specified in the "create table"-clause. The optimization affects only how the data is taken on the hard disk, the optimization shouldn't be able to change the column positions on the output recordset.
:
:
: "As far as the Primary Key is concerned, by virtue of what it is the database engine will be using this to keep track of that row, and will therefore (usually) put it first.
: Hope that helps you to understand what is going on. "
:
: The primary and other stuff should only affect in which order the DATA records will be ordered. If table T was declared so that columns were specified in the order c1, c2, ..c10, then select * should have to return the columns in the same order, c1 for first position in the recordset and c10 as the last column i nthe recordset, it caanot produse such output:
:
: "SELECT * FROM T"
: c10, c2, c3, .., c9, c1.
:
: I don't understand your explanations, maybe you didn't understand my question. I repeat: i don't ask about recors/row ordering, i am asking about columns position/order in the output.
:
: I have heard that select * can produce output with mistery column positions/orders. The data retrieved and the data's order does not interest me.
:
: Do you copy?
:
It would appear that you do not "copy". Everything I have explained IS about the COLUMNS. Read it again. The only way you can guarantee to get your colums back in the order that you want them is to name them in the SQL Query. If you use the asterisk they will come back in the order that the database engine decides is the most efficient at that particular time.
Copy ??
In a World full of blind men the one-eyed man is King

: This message was edited by Geoff-S at 2004-11-25 7:36:51

---

: : : : : : Is it possible, that "SELECT *" returns records, where columns are
: : : : : : ordered in a different order, than the order they were
: : : : : : positioned/specified when table was created?
: : : : : :
: : : : : : For example, lets assume that table T contains columns c1,c2,..c10.
: : : : : :
: : : : : : Can this be possible, that the * (asterisk) shows the columns in a
: : : : : : different orderm the c1 and c10 changed their position for example:
: : : : : :
: : : : : : query:
: : : : : :
: : : : : : "SELECT * FROM T"
: : : : : :
: : : : : : results:
: : : : : :
: : : : : : c10, c2, c3, .., c9, c1.
: : : : : :
: : : : : : I think you anderstood my question now.
: : : : : :
: : : : : : The sql-standard doesn't tell about it nothing, but i have heard that
: : : : : : "selesct *" can produce columns in a random order in some situations.
: : : : : :
: : : : : : Notice, that i'm not talking about "ORDER"-operator. I have heard that if one make a table with plenty of columns and adds plenty of indexes and constraints to the columns, then "select *" really will produce mistery column order.
: : : : : :
: : : : : :
: : : : : : Thank you for answering,
: : : : : : Regards.
: : : : : :
: : : : : Hi,
: : : : : Yes, this can sometimes happen, but USUALLY the first column (0) is your Primary Key. The best way to ensure that you get them in a specific order is to name them in the SELECT:
: : : : : EG _ "SELECT T.c1, T.c2, T.c3, ...... FROM T"
: : : : :
: : : : :
: : : : :
: : : : : In a World full of blind men the one-eyed man is King
: : : : :
: : : : :
: : : :
: : : :
: : : :
: : : :
: : : : "Yes, this can sometimes happen"
: : : :
: : : : Can you specify, in which conditions this can happen?
: : : : What should i do, to see this random-ordering? I don't understand what the primary key column position can affect to the problem.
: : : :
: : : : Thank you for answering.
: : : :
: : : I presume that you are querying on either a MSAccess or SQL_Server database, you don't actually say. But these are "Relational" Databases, not sequential. So unless you tell the database engine how you want it to fetch things it will optimise the query and fetch it in the best possible way for that particular time. This will depend very much on what else the database engine is doing at the time, so you could (in theory) run the same query 100 times over and get the results in 100 different ways. As far as the Primary Key is concerned, by virtue of what it is the database engine will be using this to keep track of that row, and will therefore (usually) put it first.
: : : Hope that helps you to understand what is going on.
: : :
: : : In a World full of blind men the one-eyed man is King
: : :
: : :
: :
: :
: : I don't understand what you said.
: :
: : "so you could (in theory) run the same query 100 times over and get the results in 100 different ways. "
: :
: : Yes, the optimization can do things, but always, the "select *" should return the columns int the order they were specified in the "create table"-clause. The optimization affects only how the data is taken on the hard disk, the optimization shouldn't be able to change the column positions on the output recordset.
: :
: :
: : "As far as the Primary Key is concerned, by virtue of what it is the database engine will be using this to keep track of that row, and will therefore (usually) put it first.
: : Hope that helps you to understand what is going on. "
: :
: : The primary and other stuff should only affect in which order the DATA records will be ordered. If table T was declared so that columns were specified in the order c1, c2, ..c10, then select * should have to return the columns in the same order, c1 for first position in the recordset and c10 as the last column i nthe recordset, it caanot produse such output:
: :
: : "SELECT * FROM T"
: : c10, c2, c3, .., c9, c1.
: :
: : I don't understand your explanations, maybe you didn't understand my question. I repeat: i don't ask about recors/row ordering, i am asking about columns position/order in the output.
: :
: : I have heard that select * can produce output with mistery column positions/orders. The data retrieved and the data's order does not interest me.
: :
: : Do you copy?
: :
: It would appear that you do not "copy". Everything I have explained IS about the COLUMNS. Read it again. The only way you can guarantee to get your colums back in the order that you want them is to name them in the SQL Query. If you use the asterisk they will come back in the order that the database engine decides is the most efficient at that particular time.
: Copy ??
: In a World full of blind men the one-eyed man is King
:
:
:
:

"If you use the asterisk they will come back in the order that the database engine decides is the most efficient at that particular time.
Copy ?? "

I would like to see, how to make the database engine to decide to show the columns in a mistery order. Then i would copy and i would roger.

: This message was edited by Geoff-S at 2004-11-25 7:36:51

---

: : : : : : Is it possible, that "SELECT *" returns records, where columns are
: : : : : : ordered in a different order, than the order they were
: : : : : : positioned/specified when table was created?
: : : : : :
: : : : : : For example, lets assume that table T contains columns c1,c2,..c10.
: : : : : :
: : : : : : Can this be possible, that the * (asterisk) shows the columns in a
: : : : : : different orderm the c1 and c10 changed their position for example:
: : : : : :
: : : : : : query:
: : : : : :
: : : : : : "SELECT * FROM T"
: : : : : :
: : : : : : results:
: : : : : :
: : : : : : c10, c2, c3, .., c9, c1.
: : : : : :
: : : : : : I think you anderstood my question now.
: : : : : :
: : : : : : The sql-standard doesn't tell about it nothing, but i have heard that
: : : : : : "selesct *" can produce columns in a random order in some situations.
: : : : : :
: : : : : : Notice, that i'm not talking about "ORDER"-operator. I have heard that if one make a table with plenty of columns and adds plenty of indexes and constraints to the columns, then "select *" really will produce mistery column order.
: : : : : :
: : : : : :
: : : : : : Thank you for answering,
: : : : : : Regards.
: : : : : :
: : : : : Hi,
: : : : : Yes, this can sometimes happen, but USUALLY the first column (0) is your Primary Key. The best way to ensure that you get them in a specific order is to name them in the SELECT:
: : : : : EG _ "SELECT T.c1, T.c2, T.c3, ...... FROM T"
: : : : :
: : : : :
: : : : :
: : : : : In a World full of blind men the one-eyed man is King
: : : : :
: : : : :
: : : :
: : : :
: : : :
: : : :
: : : : "Yes, this can sometimes happen"
: : : :
: : : : Can you specify, in which conditions this can happen?
: : : : What should i do, to see this random-ordering? I don't understand what the primary key column position can affect to the problem.
: : : :
: : : : Thank you for answering.
: : : :
: : : I presume that you are querying on either a MSAccess or SQL_Server database, you don't actually say. But these are "Relational" Databases, not sequential. So unless you tell the database engine how you want it to fetch things it will optimise the query and fetch it in the best possible way for that particular time. This will depend very much on what else the database engine is doing at the time, so you could (in theory) run the same query 100 times over and get the results in 100 different ways. As far as the Primary Key is concerned, by virtue of what it is the database engine will be using this to keep track of that row, and will therefore (usually) put it first.
: : : Hope that helps you to understand what is going on.
: : :
: : : In a World full of blind men the one-eyed man is King
: : :
: : :
: :
: :
: : I don't understand what you said.
: :
: : "so you could (in theory) run the same query 100 times over and get the results in 100 different ways. "
: :
: : Yes, the optimization can do things, but always, the "select *" should return the columns int the order they were specified in the "create table"-clause. The optimization affects only how the data is taken on the hard disk, the optimization shouldn't be able to change the column positions on the output recordset.
: :
: :
: : "As far as the Primary Key is concerned, by virtue of what it is the database engine will be using this to keep track of that row, and will therefore (usually) put it first.
: : Hope that helps you to understand what is going on. "
: :
: : The primary and other stuff should only affect in which order the DATA records will be ordered. If table T was declared so that columns were specified in the order c1, c2, ..c10, then select * should have to return the columns in the same order, c1 for first position in the recordset and c10 as the last column i nthe recordset, it caanot produse such output:
: :
: : "SELECT * FROM T"
: : c10, c2, c3, .., c9, c1.
: :
: : I don't understand your explanations, maybe you didn't understand my question. I repeat: i don't ask about recors/row ordering, i am asking about columns position/order in the output.
: :
: : I have heard that select * can produce output with mistery column positions/orders. The data retrieved and the data's order does not interest me.
: :
: : Do you copy?
: :
: It would appear that you do not "copy". Everything I have explained IS about the COLUMNS. Read it again. The only way you can guarantee to get your colums back in the order that you want them is to name them in the SQL Query. If you use the asterisk they will come back in the order that the database engine decides is the most efficient at that particular time.
: Copy ??
: In a World full of blind men the one-eyed man is King
:
:
:
:

Under the following link is an opposite declaration against your efficient-theory declarations:

http://forums.oracle.com/forums/thread.jsp?nav=false&forum=75&thread=276629&start=0&msRange=15

(you may be needed to log in there)

i copy you the text:

"John W Spencer

Given SELECT * FROM t and an unchanging definition of t, the columns will always come out in the same order as they are defined in the table. This is purely based on experience, like Andrew, I have not seen any documented statement to this effect.

I would guess that your "professional developer" was caught by someone changing the definition of the table, and breaking his code. Which is one of the many reasons you do not use SELECT * in real code.

TTFN
John"


There is an interesting sentence against yours:

"the columns will always come out in the same order as they are defined in the table."

What is the truth then?

Do you copy?

This message was edited by eigom at 2004-11-25 8:28:26

---

: : : : Is it possible, that "SELECT *" returns records, where columns are
: : : : ordered in a different order, than the order they were
: : : : positioned/specified when table was created?
: : : :
: : : : For example, lets assume that table T contains columns c1,c2,..c10.
: : : :
: : : : Can this be possible, that the * (asterisk) shows the columns in a
: : : : different orderm the c1 and c10 changed their position for example:
: : : :
: : : : query:
: : : :
: : : : "SELECT * FROM T"
: : : :
: : : : results:
: : : :
: : : : c10, c2, c3, .., c9, c1.
: : : :
: : : : I think you anderstood my question now.
: : : :
: : : : The sql-standard doesn't tell about it nothing, but i have heard that
: : : : "selesct *" can produce columns in a random order in some situations.
: : : :
: : : : Notice, that i'm not talking about "ORDER"-operator. I have heard that if one make a table with plenty of columns and adds plenty of indexes and constraints to the columns, then "select *" really will produce mistery column order.
: : : :
: : : :
: : : : Thank you for answering,
: : : : Regards.
: : : :
: : : Hi,
: : : Yes, this can sometimes happen, but USUALLY the first column (0) is your Primary Key. The best way to ensure that you get them in a specific order is to name them in the SELECT:
: : : EG _ "SELECT T.c1, T.c2, T.c3, ...... FROM T"
: : :
: : :
: : :
: : : In a World full of blind men the one-eyed man is King
: : :
: : :
: :
: :
: :
: :
: : "Yes, this can sometimes happen"
: :
: : Can you specify, in which conditions this can happen?
: : What should i do, to see this random-ordering? I don't understand what the primary key column position can affect to the problem.
: :
: : Thank you for answering.
: :
: I presume that you are querying on either a MSAccess or SQL_Server database, you don't actually say. But these are "Relational" Databases, not sequential. So unless you tell the database engine how you want it to fetch things it will optimise the query and fetch it in the best possible way for that particular time. This will depend very much on what else the database engine is doing at the time, so you could (in theory) run the same query 100 times over and get the results in 100 different ways. As far as the Primary Key is concerned, by virtue of what it is the database engine will be using this to keep track of that row, and will therefore (usually) put it first.
: Hope that helps you to understand what is going on.
:
: In a World full of blind men the one-eyed man is King
:
:

What about this:

SQL Server:

Specifies that all columns from all tables and views in
the FROM clause should be returned. The columns are returned
by table or view, as specified in the FROM clause, and in the
order in which they exist in the table or view.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp

Sybase:

* retrieves the data in create table order.
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/5072;pt=5072#X


Do you copy that?

Over.

This message was edited by eigom at 2004-11-25 8:58:29

---

: : : : Is it possible, that "SELECT *" returns records, where columns are
: : : : ordered in a different order, than the order they were
: : : : positioned/specified when table was created?
: : : :
: : : : For example, lets assume that table T contains columns c1,c2,..c10.
: : : :
: : : : Can this be possible, that the * (asterisk) shows the columns in a
: : : : different orderm the c1 and c10 changed their position for example:
: : : :
: : : : query:
: : : :
: : : : "SELECT * FROM T"
: : : :
: : : : results:
: : : :
: : : : c10, c2, c3, .., c9, c1.
: : : :
: : : : I think you anderstood my question now.
: : : :
: : : : The sql-standard doesn't tell about it nothing, but i have heard that
: : : : "selesct *" can produce columns in a random order in some situations.
: : : :
: : : : Notice, that i'm not talking about "ORDER"-operator. I have heard that if one make a table with plenty of columns and adds plenty of indexes and constraints to the columns, then "select *" really will produce mistery column order.
: : : :
: : : :
: : : : Thank you for answering,
: : : : Regards.
: : : :
: : : Hi,
: : : Yes, this can sometimes happen, but USUALLY the first column (0) is your Primary Key. The best way to ensure that you get them in a specific order is to name them in the SELECT:
: : : EG _ "SELECT T.c1, T.c2, T.c3, ...... FROM T"
: : :
: : :
: : :
: : : In a World full of blind men the one-eyed man is King
: : :
: : :
: :
: :
: :
: :
: : "Yes, this can sometimes happen"
: :
: : Can you specify, in which conditions this can happen?
: : What should i do, to see this random-ordering? I don't understand what the primary key column position can affect to the problem.
: :
: : Thank you for answering.
: :
: I presume that you are querying on either a MSAccess or SQL_Server database, you don't actually say. But these are "Relational" Databases, not sequential. So unless you tell the database engine how you want it to fetch things it will optimise the query and fetch it in the best possible way for that particular time. This will depend very much on what else the database engine is doing at the time, so you could (in theory) run the same query 100 times over and get the results in 100 different ways. As far as the Primary Key is concerned, by virtue of what it is the database engine will be using this to keep track of that row, and will therefore (usually) put it first.
: Hope that helps you to understand what is going on.
:
: In a World full of blind men the one-eyed man is King
:
:


I presented you the facts. Are you copying? Why are you not answering? Where are you currently copying?

It seems that you are just a theorist. When it comes to facts then you know nothing. You are not copying.

Over.

eigom says -
: I presented you the facts. Are you copying? Why are you not answering? Where are you currently copying?
:
: It seems that you are just a theorist. When it comes to facts then you know nothing. You are not copying.
:
: Over.
:
Hi eigom.
I was currently copying at the link in your previous post to the T-SQL "SELECT" page at MS. Well, there you go then, they should all stay in the same order as defined in the table. I had in the back of my mind an article about the Jet 3.5 engine that I read a long time ago, I suppose if I had "copied" it then I would have got it right.
So, estrose, eigom has answered your query. Columns will stay the same unless someone messes with the table definition.
And on that I think I should hastily make it
Over AND Out.

In a World full of blind men the one-eyed man is King

: eigom says -
: : I presented you the facts. Are you copying? Why are you not answering? Where are you currently copying?
: :
: : It seems that you are just a theorist. When it comes to facts then you know nothing. You are not copying.
: :
: : Over.
: :
: Hi eigom.
: I was currently copying at the link in your previous post to the T-SQL "SELECT" page at MS. Well, there you go then, they should all stay in the same order as defined in the table. I had in the back of my mind an article about the Jet 3.5 engine that I read a long time ago, I suppose if I had "copied" it then I would have got it right.
: So, estrose, eigom has answered your query. Columns will stay the same unless someone messes with the table definition.
: And on that I think I should hastily make it
: Over AND Out.
:
: In a World full of blind men the one-eyed man is King
:
:



"Columns will stay the same unless someone messes with the table definition. "

Yes. Thank you. Of course, if somebody creates his own tiny little database engine, then one can implement the mystery column ordering there. Mainstream databases like Oracle, SQL-Server and other does not currently have this mystery-feature.

Over.