SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    Modesto, CA
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL: Group By & MAX() Question

    Howdy again.. its been awhile.. so here goes.

    I want to do a Querry like this.
    PHP Code:
    SELECT FROM 'uploads' GROUP BY 'Name' HAVING Date MAX(Date
    Code:
    Here is the Upload DB.
    
    ID Name   Doc        Date
    1  Fruki  index.php  2002-07-01
    2  Imex   core.php   2002-07-01
    3  Imex   admin.php  2002-07-01
    4  Imex   admin.php  2002-07-01
    5  Sani   code_3.php 2002-07-02
    6  Fruki  index.php  2002-07-03
    7  Sani   index.php  2002-07-04
    and have the output be like this (IE: Having the Dates displayed be the NEWEST entry)
    Code:
    6  Fruki  index.php  2002-07-03
    4  Imex   admin.php  2002-07-01
    7  Sani   index.php  2002-07-04
    Ok, I have tried this exact querry in many different ways and all I get is the FIRST entry with the name. When I want it to display the Last Entry that was submited. Can anyone help me?

  2. #2
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am afraid you need a sub select, which mysql currently does not support. You should do in two parts,

    SELECT name,MAX(DATE) FROM uploads GROUP BY name

    and after you've got the results use run

    SELECT id,doc FROM uploads WHERE name=the_name_returned AND DATE=the_date_returned

    for each one.

    Paul

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    Modesto, CA
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    k, but there is still a problem its giving me the First entry of every name instead of the last entry of every name. Its printing out the * ones instead of the ~ ones. Is there a way to do like GROUP BY name DESC? so I can get the last entry first?

    Code:
    *1  Fruki  index.php  2002-07-01
    *2  Imex   core.php   2002-07-01
    3  Imex   admin.php  2002-07-01
    ~4  Imex   admin.php  2002-07-01
    *5  Sani   code_3.php 2002-07-02
    ~6  Fruki  index.php  2002-07-03
    ~7  Sani   index.php  2002-07-04

  4. #4
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fruki,

    This is what I get after running the "SELECT name,MAX(DATE) FROM uploads GROUP BY name" query,
    Code:
    mysql> select name,max(date) from uploads group by name;
    +-------+------------+
    | name  | max(date)  |
    +-------+------------+
    | Fruki | 2002-07-03 |
    | Imex  | 2002-07-01 |
    | Sani  | 2002-07-04 |
    +-------+------------+
    That is what you need, isn't it?
    Paul

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    Modesto, CA
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yup.. maybe I did it wrong.. I will test it again.. Thanks for your help!
    Last edited by Imex; Jul 21, 2002 at 22:46.

  6. #6
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem. If you have further problems just post you code.

    Paul


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
  •