As Ines says, relations (what we think of as tables) in SQL are more like sets than tables of columns and rows. The order of fields in a relation is not guaranteed or even specified. This is why the select clause even exists in the first place, because you are constructing the output by column name. Now, it just so happens that most DBs I have used will order columns in 'select * from' in the same order as the original create table statement (if not modified by adding or removing columns by /alter table.) /
It is perfectly legal for DB engines to output * in any order, although probably none do. Remember 'select' is the *just* output constructor. 'select 2' just means construct the number 2 and output it. I could do "select 'MyName' from table1" and not specify any field. If I specify a field via an alias, as in select foo as 1, bar as 2, bat as 3 that will just name the fields on the output and you could do some shell-fu on that. But I suspect that is not what you need. SQL tables are not tabular data sets. Never were, despite the name. Were always relations of attributes[1] Ed [1] http://www.acm.org/classics/nov95/toc.html (Original paper by E.F. Codd - SQL's Daddy. Section 1.3) [2] Interview with Chris Date (SQL's other Dad) http://www.oreillynet.com/pub/a/network/2005/07/29/cjdate.html Ines Sombra wrote: > Robert, > > Being able to reference the column by position violates relational > theory and I doubt any DBMS would support it. However you could do > something quick and nasty if you use a "shellish" environment to access > your db (such as sqsh for sybase-like dbs). > > Ines > > Robert Citek wrote: > >> Is it possible to select fields in a table by position? >> >> Let's say I have a table "foobar" like so: >> >> foo bar bat >> a 1 x >> b 2 y >> c 3 z >> >> I can select the bar field with SQL like so: >> >> select bar from foobar; >> >> Since bar is the second field in the table, I was wondering if I can >> select fields using the numerical position: >> >> select 2 from foobar; >> >> Unfortunately, that simply returns the value of "2" for every >> record. A search through my Oracle, PostgreSQL, and MySQL books did >> not mention that this can be done, but maybe it can and I'm just >> overlooking something. >> >> Regards, >> - Robert >> http://www.cwelug.org/downloads >> Help others get OpenSource software. Distribute FLOSS >> for Windows, Linux, *BSD, and MacOS X with BitTorrent >> >> >> >> _______________________________________________ >> CWE-LUG mailing list >> [email protected] >> http://www.cwelug.org/ >> http://www.cwelug.org/archives/ >> http://www.cwelug.org/mailinglist/ >> > > -- Ed Howland WDT Solutions, LLC. [EMAIL PROTECTED] (314) 962-0766 _______________________________________________ CWE-LUG mailing list [email protected] http://www.cwelug.org/ http://www.cwelug.org/archives/ http://www.cwelug.org/mailinglist/
