Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: Max/Min dates from records

    Code:
    +----+------------+
    | id | date       |
    +----+------------+
    |  0 | 2006-01-01 |
    |  1 | 2006-01-10 |
    |  2 | 2006-01-15 |
    |  3 | 2006-01-05 |
    |  4 | 2006-01-03 |
    |  5 | 2006-01-02 |
    +----+------------+
    I'd like to get the id for the last date.
    Maybe by doing something like that:
    Code:
    SELECT
     t.id
    FROM
     table AS t
    WHERE
     t.date = MAX(t.date)
    This query is not valid.
    How can I do it ?

  2. #2
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Do I have to do
    Code:
    SELECT
     t.id
    FROM
     table AS t
    ORDER BY
     t.date DESC
    LIMIT 1
    ?

  3. #3
    Join Date
    Dec 2005
    Location
    Arnhem, Gld, NL
    Posts
    21
    i thought this could work,..
    Code:
    select id from (select max(date) from table)

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your could try the "top" clause in the select
    with an order by <mycolumn> DESC;

    select top 1 <mydatecolumn> from <mytable> order by <mydatecolumn> DESC;
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Are you sure that TOP is implemented in MySQL ?
    Ok now let's imagine that content
    Code:
    +----+------------+------+
    | id | date       | user |
    +----+------------+------+
    |  0 | 2006-01-01 |    5 |
    |  1 | 2006-01-10 |    6 |
    |  2 | 2006-01-15 |    2 |
    |  3 | 2006-01-05 |    2 |
    |  4 | 2006-01-03 |    6 |
    |  5 | 2006-01-02 |    0 |
    +----+------------+------+
    How can we get the id for the last date for each user.
    Which should give us:
    Code:
    +----+------+
    | id | user |
    +----+------+
    |  0 |    5 |
    |  1 |    6 |
    |  2 |    2 |
    |  5 |    0 |
    +----+------+
    Now I'm not sure that this query works:
    Code:
    SELECT DISTINCT
     t.id AS "id",
     t.user AS "user"
    FROM
     table AS t
    ORDER BY
     t.date

  6. #6
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Isn't the question clear ?

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    steer clear of distinct whereever possible:

    SELECT id,user
    FROM <table> t
    WHERE t.date=(SELECT MAX(date) FROM <table> WHERE id=t.id)


    WHAT IS THE NAME OF YOUR TABLE? IT'S NOT "TABLE" IS IT?
    in the sql above replace <table> with the actual name of your table.

  8. #8
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Ok so we are obliged to do a sub-select to get this.

  9. #9
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    You're not obliged to do anything of course. I'm just writing out your query another way ... Using a correlated subquery.

    I think another way to do this would be to use a GROUP BY clause:

    SELECT id,user,MAX(date)
    FROM <table> t
    GROUP BY date,user

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Actually having tested that query above I can say it doesn't work... Actually neither of the queries I supplied above work correctly. It's always handy to try these things out

  11. #11
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    SELECT id,user,MAX(date)
    FROM <table> t
    GROUP BY user
    ORDER BY t.date

    That works ok and doesn't have a distinct in it...

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gtk
    Ok so we are obliged to do a sub-select to get this.
    what's the problem, are you not on 4.1 yet? if not, why not?
    Code:
    select t1.id 
         , t1.date
         , t1.user
      from daTable as t1
    inner
      join daTable as t2
        on t2.user = t1.user
    group
        by t1.id 
         , t1.date
         , t1.user
    having t1.id = max(t2.id)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aschk
    That works ok and doesn't have a distinct in it...
    no, it only appears to work okay

    it's actually invalid, and the value of the id is unpredictable

    see GROUP BY and HAVING with Hidden Fields

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Here is the CORRECT correlated query:

    SELECT id,user
    FROM <table> t
    WHERE t.date=(SELECT MAX(date) FROM <table> where `user`=t.`user`);

    That's what I should have written the first time. I was comparing on id and it should have been user... silly me.

    Edit: Using a BTREE index on user and date (ALTER TABLE ADD INDEX(`user`,`date`)) improves correlated subquery causing ONLY 1 row lookup.
    Last edited by aschk; 03-30-07 at 07:22.

  15. #15
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    select t1.id
    , t1.date
    , t1.user
    from daTable as t1
    inner
    join daTable as t2
    on t2.user = t1.user
    group
    by t1.id
    , t1.date
    , t1.user
    having t1.id = max(t2.id)
    This query gives the wrong result. I believe he is looking for the ID and User where the date is greatest. Not where the ID is greatest.

Posting Permissions

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