MySQL select all last added query

Question

Im trying to select all the items that are recently added.

I have to find what is the last added exam (sometimes more that one as shown below)

Here is an example of the DB.

id  name                start_date  end_date    duration
4   Exam August 2011    24.8.2011   0000-00-00  20
3   Exam July 2011      28.7.2011   0000-00-00  20
5   Exam August 2011    24.8.2011   0000-00-00  20
6   Exam August 2011    24.8.2011   0000-00-00  20
25  Exam September 2011 26.9.2011   07.10.2011  20
26  Exam September 2011 26.9.2011   07.10.2011  20
27  Exam September 2011 26.9.2011   07.10.2011  20

And here is the query that im using, my question is is there a better way to do it? Some optimization?

SELECT * FROM me_tests WHERE name = (   
    SELECT name FROM me_tests ORDER BY start_date DESC LIMIT 1
)

The second query will find the name of the last added (same as start date) one, will pass it to the first select and will select all the other tests based on the name.

Why can't you just `SELECT` based on `start_date`? Wouldn't that be easier than running two queries? — sdleihssirhc, Sep 29 '11 at 15:35

Bhesh Gurung · Answer 1 · 2011-09-29T15:43:23.853

3

SELECT * FROM me_tests ORDER BY start_date DESC LIMIT 1

Or -

SELECT * FROM me_tests 
WHERE start_date =
(SELECT MAX(start_date) FROM me_tests);

edited Sep 29 '11 at 15:43

answered Sep 29 '11 at 15:37

Bhesh Gurung

50,430
22
93
142

I cannot limit 1 because as you can see i have 3 rows added on 26.09.2011 – Boris Sep 29 '11 at 15:40
The second one will work, but I think that from performance, optimization, point of view it will be the same? Or not? – Boris Sep 29 '11 at 15:48
@Boris: I think you need to use the the start_date on the condition not the name. – Bhesh Gurung Sep 29 '11 at 15:50
It makes sence but in my case using name is better i think. Why? Different departments may have different starting dates for the exam (and in this case i will not be able to find all of them), but the name of the exam is one and is auto generated using the current month and year. There might be a better way but i cannot figure it out now. – Boris Sep 29 '11 at 15:55

score 1 · Answer 2 · answered Sep 29 '11 at 15:37

1

I would use

SELECT * FROM me_tests ORDER BY start_date DESC LIMIT 1

... except using * is bad, so you should name all the columns you want to retrieve.

answered Sep 29 '11 at 15:37

Kalessin

2,282
2
22
24

I cannot limit 1 because as you can see i have 3 rows added on 26.09.2011. Thanks for the advice regarding *. – Boris Sep 29 '11 at 15:40
So you want the last three? Or do you want to `ORDER BY start_date DESC, id DESC LIMIT 1`? – Kalessin Sep 29 '11 at 15:46
I want the last added ones (might be one, might be more than one). And actually i wanted to know if there is a better (from performance) point of view than what i did. – Boris Sep 29 '11 at 15:51

score 1 · Accepted Answer · answered Sep 29 '11 at 15:39

1

What if the last added items were added at the same time but have different names?

In that case I would think you would want a query like this.

SELECT 
  * 
FROM me_tests 
WHERE start_date = (SELECT 
                      start_date
                    FROM me_tests 
                    ORDER BY start_date DESC LIMIT 1)

answered Sep 29 '11 at 15:39

John Hartsock

85,422
23
131
146

Very nice. The name cannot be different (auto generated using current month and year). However i really appreciate your alternative solution. Is it going to be faster or there will be no difference? – Boris Sep 29 '11 at 15:44
1

@Boris ... Speed of this query versus yours would depend on indices that you have. I would consider creating an index on start_date. – John Hartsock Sep 29 '11 at 16:02

MySQL select all last added query

3 Answers3