SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    what is the syntax for ordering field ASC/DESC when creating table?

    example

    Code MySQL:
     
    CREATE TABLE sample_table (
      field_one VARCHAR DEFAULT NULL,
      field_two INT UNSIGNED DEFAULT NULL,
      .
      .
      .
      . )TYPE=MyISAM

    I want 'field_two' to order by ASC/DESC

    instead of alter table order AFTER creating the table in phpMyAdmin,



    how to explicitly order any field ASC/DESC while creating table?

    CREATE TABLE sample_table (
    field_one VARCHAR DEFAULT NULL,
    field_two INT UNSIGNED DEFAULT NULL ORDER BY ASC,
    .
    .
    .
    . )TYPE=MyISAM

    ??

    of course not like this...

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    That's because you order on a select of the values from the table, not on the table itself

    SELECT field_one, field_two FROM sample_table ORDER BY field_two ASC
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    as you might know, ALTER TABLE works by making a temporary copy of the original table

    the ALTER TABLE... ORDER BY ... option is used only to resequence the rows copied to the temporary table

    this sequence is not guaranteed and will start to fall apart as soon as inserts or updates are made to the altered table

    the only way to get rows in the sequence you want is SELECT ... ORDER BY ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    That's because you order on a select of the values from the table, not on the table itself

    SELECT field_one, field_two FROM sample_table ORDER BY field_two ASC
    been using SELECT .....ORDER BY field ASC many times. i know this.

    what i really want to do is to order 2 or more sequences...

    if it is supported by MySQL, what i mean would probably look like

    SELECT ..... ORDER BY field_two DESC field_three ASC

    yeah! i need to order more than one sequence, one DESC another ASC.

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,810
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by leelong View Post
    SELECT ..... ORDER BY field_two DESC field_three ASC
    You left out a comma

    SELECT ..... ORDER BY field_two DESC, field_three ASC
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    You left out a comma

    SELECT ..... ORDER BY field_two DESC, field_three ASC
    ahaaa silly me...

    2 < 2(?)3 < 3

    what is the math operator for (?) for the statement to become true?

    log ? cos ? sin ? tan ? ln ? log_e ?? +(plus),-(minus),*(time),/(divide) ........etc ??

    example putting a ' - (minus)' into (?), 2 < (2-3) < 3 that is 2 < -1 < 3, this is not true.


Bookmarks

Posting Permissions

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