Results 1 to 4 of 4

Thread: SELECT help

  1. #1
    Join Date
    Sep 2003
    Location
    Fiddletown, CA
    Posts
    2

    Unanswered: SELECT help

    Hello all.

    I'm trying to achieve what i believe can be done with one SQL directive (and is probably really simple), but I'm really having trouble finding it!

    I have the following table:

    mysql> select date, uid, subject from log;
    +------------+------+----------------------+
    | date | uid | subject |
    +------------+------+----------------------+
    | 1064559600 | 1 | Test post |
    | 1064732400 | 1 | Tested new artillery |
    | 1036828800 | 1 | Old log entry |
    | 1064559600 | 2 | Test 2 |
    | 1033399830 | 2 | Old test |
    | 1064646000 | 1 | This is the latest |
    | 1064559600 | 1 | Spam Checkers |
    +------------+------+----------------------+
    7 rows in set (0.00 sec)


    What I'd like to do is end up with a result that picks the latest post for each user. This is what it *should* look like:

    +------------+------+----------------------+
    | date | uid | subject |
    +------------+------+----------------------+
    | 1064732400 | 1 | Tested new artillery |
    | 1064559600 | 2 | Test 2 |
    +------------+------+----------------------+


    So far the closest I've come is using:

    mysql> select date, uid, subject from log group by uid order by date;
    +------------+------+-----------+
    | date | uid | subject |
    +------------+------+-----------+
    | 1064559600 | 1 | Test post |
    | 1064559600 | 2 | Test 2 |
    +------------+------+-----------+
    2 rows in set (0.01 sec)


    Which is the wrong info. Looks like it just picks the first match for the uid and uses that. That or the 'order' directive isn't being used at all (since dropping 'order by date' results in the same thing). I get an error when I swap the 'order' and 'group by' directives.

    I tried MAX(date), but it didn't find the row that had the max date, but instead used the same results as above except substituted the max date from the list. Not very useful.

    What am I missing?

    Thanks.
    #MikeC

  2. #2
    Join Date
    Sep 2003
    Posts
    69
    Give this a try:

    Code:
    
    SELECT date, DISTINCT(uid), subject 
    FROM log
    ORDER BY date ASC;

  3. #3
    Join Date
    Sep 2003
    Location
    Fiddletown, CA
    Posts
    2
    Thanks!

    Looks like I need to upgrade to 4.x first tho.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, a subselect will do it, and you need 4.1 for that

    or you could use a temp table

    see 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Field

    rudy
    http://r937.com/

Posting Permissions

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