Results 1 to 4 of 4

Thread: Advanced query?

  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Advanced query?

    Hi to all,

    I have got a table with two fileds a type field and a timestamp field.

    I would like to know if it is possible to get , with one query, the last in date for each type of record.

    the query on this table:
    Code:
    +--------------+----------------+
    | type         |    insertdate  |
    +--------------+----------------+
    |           23 | 20040331101955 |
    |           23 | 20040331102130 |
    |            8 | 20031217173254 |
    |           23 | 20040331101758 |
    |           23 | 20040331101758 |
    |            8 | 20031202114923 |
    |           23 | 20040331101124 |
    |           23 | 20040331101515 |
    |           23 | 20040331100833 |
    |           23 | 20040331101001 |
    |           23 | 20040331100624 |
    |           29 | 20040329150043 |
    |           23 | 20040331100423 |
    |           18 | 20040329094144 |
    |            8 | 20031201105042 |
    |            8 | 20031202115245 |
    |            8 | 20031202115200 |
    |            8 | 20031202123441 |
    |            8 | 20031202124028 |
    |            8 | 20040316105150 |
    |            8 | 20031203000005 |
    |            8 | 20031203100727 |
    |            8 | 20031203100659 |
    |           15 | 20031203101629 |
    |           15 | 20031203101816 |
    |           15 | 20031203101932 |
    |           15 | 20031203102042 |
    |           15 | 20031203102203 |
    +--------------+----------------+
    must give:
    Code:
    +--------------+----------------+
    | type         |    insertdate  |
    +--------------+----------------+
    |           23 | 20040331101758 |
    |           29 | 20040329150043 |
    |           18 | 20040329094144 |
    |            8 | 20040316105150 |
    |           15 | 20031203102203 |
    +--------------+----------------+

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select t1.type
         , t1.insertdate
      from yourtable as t1
    inner
      join yourtable as t2
        on t1.type = t2.type
    group
        by t1.type
         , t1.insertdate
    having t1.insertdate  
         = max(t2.insertdate)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    4

    hmm.... ok

    Very thanks for your answer, but I have still some problems: on my 3000+ record table this query takes 46 seconds to execute (the type and insertdate fileds are already indexed) I think it's the HAVING clause that slows down everything.

    I was trying to understand your query when trying this one

    SELECT max( insertdate ) , type
    FROM t1
    GROUP BY type

    and magically it returned the same results as yours but only in 0.1 secs, but I would like to know if these two queries are equivalent or not.

    I remeber having already tried something similar but without success, and now I am confused, can you help me understand?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, they are equivalent, as long as you do not try to select columns that you aren't grouping on

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

Posting Permissions

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