#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    18
    Rep Power
    0
    I've been twisting my brain on this one, and don't know if this can be done.
    I'm trying to sort a database by two columns, date and name (numerical and alphabetical, in that order) The problem is that when I use
    SELECT * FROM db ORDER BY date, name DESC
    only the variable preceding DESC is sorted descending. I want the date field to be descending, and then order alphabetically within equal dates. Of course, SELECT * FROM db ORDER BY name, date DESC doesn't work, since the name (alphabetically) comes first. Am I just blind here, or can't this be done?

    (An example :
    2/11-00 Al
    2/11-00 ****
    2/11-00 Zorro
    1/11-00 Abe
    1/11-00 Wesley
    9/10-00 ****
    Etc...)


    <UL TYPE=SQUARE>
    <LI>Metus Mortuus - Support my experimental music project
    </UL>
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    Each field in the sort clause needs to be specified with DESC or it will default to ASC.

    select * from db order by date desc,name desc;
    Also, I take it from the format that DATE is not one of the date/time datatypes? It looks like a text type and the format it is in will not sort by date.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    61
    Rep Power
    14
    set your date and time columns to int(11) or something like that. Then, you insert time() which gives the seconds since Jan 1, 1970. When you pull it out of the table, you just run it through the date() function, and you're all set.
  6. #4
  7. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,640
    Rep Power
    4476
    Just store MySQL timestamps. You'll add more work for yourself by trying to do everything in unix timestamps. MySQL provides plenty of date functions to get your data in any form, even a unix_timestamp if absolutely neccessary.

    If this isn't a large table, change your column to a timestamp or date column and update your data. If there is a lot of data, you might have to write a script to do it for you.

    Then, do like rod said and put a DESC for each clause in your ORDER BY

    ORDER BY date DESC, name DESC

    ---John Holmes...
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    18
    Rep Power
    0
    Hmm, I'm not sure why you drew the conclusion that I'm using a string for dates. I'm using datetime, and it's working wonderfully. The only thing I was wondering about was the syntax of DESC (didn't know I could put it after each variable, like rod suggested). I browsed through the manual at mysql.com several times, but couldn't find this anywhere. Strange.
    Anyway, it works now.
    Thanks for your help all.

    <UL TYPE=SQUARE>
    <LI>Metus Mortuus - Support my experimental music project
    </UL>
  10. #6
  11. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,640
    Rep Power
    4476
    I'm sure assumed that because of your weird date syntax '2/11-00' I don't even know what that is..Feb 11th? Nov 2nd?

    select * from your_table order by date desc, name;

    That will work if it's a date column...probably what you're using...

    ---John Holmes...

Similar Threads

  1. Generating external js subcategory list from MySQL using PHP
    By NanoEntity in forum PHP Development
    Replies: 4
    Last Post: December 15th, 2003, 09:50 AM
  2. PHP-MYSQL Query Help
    By Nite_Shift in forum PHP Development
    Replies: 2
    Last Post: November 17th, 2003, 06:34 AM
  3. bug with nested order bys
    By cablehead in forum MS SQL Development
    Replies: 2
    Last Post: October 27th, 2003, 03:57 PM
  4. MySQL desc order query solving problem
    By dssachdeva in forum MySQL Help
    Replies: 3
    Last Post: September 18th, 2003, 02:52 AM
  5. Forcing key read in reverse order?
    By lanzer in forum MySQL Help
    Replies: 7
    Last Post: September 15th, 2003, 09:19 PM

IMN logo majestic logo threadwatch logo seochat tools logo