CODEWITHSUNDEEP
mysqlselectsubquery
Boris
Boris

Reputation: 749

MySQL select all last added query

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.

Upvotes: 1

Views: 259

Answers (3)

Bhesh Gurung
Bhesh Gurung

Reputation: 51030

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);

Upvotes: 3

John Hartsock
John Hartsock

Reputation: 86882

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)

Upvotes: 1

Kalessin
Kalessin

Reputation: 2302

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.

Upvotes: 1

Related Questions