Advanced query?

ominobufo · #1 (**permalink**) 10-15-04, 06:16

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 |
+--------------+----------------+

r937 · #2 (**permalink**) 10-15-04, 06:48

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)

ominobufo · #3 (**permalink**) 10-15-04, 12:04

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?

r937 · #4 (**permalink**) 10-15-04, 13:25

yes, they are equivalent, as long as you do not try to select columns that you aren't grouping on

Internet Services

Web Development

Digital Marketing

Consumer Tech