Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Is it possible, that "SELECT *" returns records, where columns are

estroseestrose Posts: 32Member
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.
«1

Comments

  • Geoff-SGeoff-S Posts: 82Member
    : 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"
    :-)


    [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)

  • estroseestrose Posts: 32Member
    : : 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"
    : :-)
    :
    :
    : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    :
    :




    "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.
  • Geoff-SGeoff-S Posts: 82Member
    : : : 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"
    : : :-)
    : :
    : :
    : : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    : :
    : :
    :
    :
    :
    :
    : "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.
    :-)
    [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)

  • estroseestrose Posts: 32Member
    : : : : 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"
    : : : :-)
    : : :
    : : :
    : : : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    : : :
    : : :
    : :
    : :
    : :
    : :
    : : "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.
    : :-)
    : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    :
    :


    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?
  • Geoff-SGeoff-S Posts: 82Member
    [b][red]This message was edited by Geoff-S at 2004-11-25 7:36:51[/red][/b][hr]
    : : : : : 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"
    : : : : :-)
    : : : :
    : : : :
    : : : : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    : : : :
    : : : :
    : : :
    : : :
    : : :
    : : :
    : : : "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.
    : : :-)
    : : [blue][b]In a World full of blind men the one-eyed man is King[/b][/blue];-)
    : :
    : :
    :
    :
    : 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 ??
    [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)



  • estroseestrose Posts: 32Member
    : [b][red]This message was edited by Geoff-S at 2004-11-25 7:36:51[/red][/b][hr]
    : : : : : : 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"
    : : : : : :-)
    : : : : :
    : : : : :
    : : : : : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    : : : : :
    : : : : :
    : : : :
    : : : :
    : : : :
    : : : :
    : : : : "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.
    : : : :-)
    : : : [blue][b]In a World full of blind men the one-eyed man is King[/b][/blue];-)
    : : :
    : : :
    : :
    : :
    : : 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 ??
    : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    :
    :
    :
    :

    "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.



  • eigomeigom Posts: 2Member
    [b][red]This message was edited by eigom at 2004-11-25 8:28:26[/red][/b][hr]
    : : : : 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"
    : : : :-)
    : : :
    : : :
    : : : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    : : :
    : : :
    : :
    : :
    : :
    : :
    : : "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.
    : :-)
    : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    :
    :

    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.



  • estroseestrose Posts: 32Member
    : [b][red]This message was edited by Geoff-S at 2004-11-25 7:36:51[/red][/b][hr]
    : : : : : : 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"
    : : : : : :-)
    : : : : :
    : : : : :
    : : : : : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    : : : : :
    : : : : :
    : : : :
    : : : :
    : : : :
    : : : :
    : : : : "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.
    : : : :-)
    : : : [blue][b]In a World full of blind men the one-eyed man is King[/b][/blue];-)
    : : :
    : : :
    : :
    : :
    : : 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 ??
    : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    :
    :
    :
    :

    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?
  • eigomeigom Posts: 2Member
    [b][red]This message was edited by eigom at 2004-11-25 8:58:29[/red][/b][hr]
    : : : : 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"
    : : : :-)
    : : :
    : : :
    : : : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    : : :
    : : :
    : :
    : :
    : :
    : :
    : : "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.
    : :-)
    : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    :
    :


    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.


  • Geoff-SGeoff-S Posts: 82Member
    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.

    [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)

«1
Sign In or Register to comment.