Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: How to SELECT by column number ?

    I want to select with column order without field name .
    Is it possible ?

    SQL2000 db
    ...

    I want to select a field by its order in table, but without naming it to have a constant name of it, like :

    select (column01) as L1 from myTable
    Last edited by monjohia; 12-07-04 at 07:46.

  2. #2
    Join Date
    Feb 2004
    Posts
    88
    Why didn't you just try it and find out ?

    select * from mytable order by 1, 3, 5

    is that what you mean? it works...

    HTH,

    Bill

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You'd need to look up the name of the column in the system tables and generate dynamic sql to perform your query.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    select (column01) as L1 from myTable order by L1
    or
    select (column01) as L1 from myTable order by 1
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    well hell!
    how about
    create table
    (
    1 int not null
    ,2 varchar(20) null
    ,3 int null
    )

    and then select into the table

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "select (column01) as L1 from myTable order by 1"???

    Server: Msg 207, Level 16, State 3, Line 1
    Invalid column name 'column01'. !!!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I don't think the poster is asking for the ORDER BY clause.
    Code:
    create procedure dbo.foo (@colnumber int = null) as
    if @colnumber is null (select * from authors)
    else if @colnumber = 1 (select au_id from authors)
    else if @colnumber = 2 (select au_lname from authors) 
    else if @colnumber = 3 (select au_fname from authors) 
    else if @colnumber = 4 (select phone from authors) 
    else if @colnumber = 5 (select address from authors) 
    else (select col1 = 'NOTHING' from authors /*there*/ where 1=2)
    return (0)
    go
    Last edited by rdjabarov; 12-07-04 at 14:40. Reason: WHERE not THERE...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •