Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    29

    Unanswered: Modify Sort Order

    Hi All
    Pls Is There A Way Of Changing/modifying The Sort Order Of The Data (columns) In An Oracle 10g Table. The Table Already Exists But We Want To Rearrange The Table To Reflect A Particular Sort Order. Any Clues Please? Can Alter Table Statement.......... Work?
    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the rows in relational tables have no order

    the only order you can get is by using ORDER BY in the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    If you want to re-arrange the order of the columns, simply change your SELECT statement.
    Code:
    SELECT firstname, lastname FROM person
    to
    Code:
    SELECT lastname, firstname FROM person

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    read the question wrong, please disregard ---=cf

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I believe that Olusoga has this situation:
    Code:
    CREATE TABLE test
    (birthdate date,
     id number,
     name varchar2(20));
    but would like to rearrange it to
    Code:
    CREATE TABLE test
    (id number,
     name varchar2(20),
     birthdate date);
    If so, well don't bother; order of columns in a table doesn't matter. It is irrelevant how the columns are ordered in a table. You'll order them for reporting purposes, just like Shammat told you.

    If you'd, however, want to rearrange a table, you'll first have to drop it and create a new copy. This process might have several paths (CTAS, drop the original table, rename a new one to the old name), alter table to add columns, update it and drop old columns, etc. but that's too much pain for no gain. Don't forget the fact that you might need to drop / invalidate constraints as well (and this might really be painful).

    Also, you might take a look at the DBMS_REDEFINITION package if your database version supports it (9i onwards if I'm not wrong).

Posting Permissions

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