Get most recent date records from mysql

Question

tbl_contacts: -

user_id - int
contact_id - int
first_name - varchar
last_name - varchar
date_backup - TIMESTAMP

I am having lots of data and i want to get the latest one from the database.

Currently I am having data of 2 different dates, 1 is 2014-02-12 04:47:39 and another is 2014-01-12 04:47:39. I am having total 125 records from which 5 are of 2014-01-12 04:47:39 date and rest are of 2014-02-12 04:47:39. I am using below query to get the latest date data but its returning all the data somehow. I am trying since long and unable to successfully achieve my goal. If anyone has any idea please kindly help me.

Query

 SELECT `contact_id`, `user_id`, `date_backup`, `first_name`, `last_name`
  FROM tbl_contacts WHERE `date_backup` IN (
    SELECT MAX(`date_backup`)
      FROM tbl_contacts WHERE `user_id`= 1 GROUP BY `contact_id`
  )
  ORDER BY `contact_id`ASC, `date_backup` DESC

By using ORDER BY date_backup DESC, I am getting the old data at the end of list. But i just don't want the old date record at all if new date record is available.

try: `ORDER BY date_backup DESC, contact_id ASC` . – 124 Feb 12 '14 at 12:52 — 124, Feb 12 '14 at 12:52
Not working, its still showing old records at the end. – Scorpion Feb 12 '14 at 12:56 — Scorpion, Feb 12 '14 at 12:56

score 0 · Answer 1 · answered Feb 12 '14 at 13:14

user MySql UNIX_TIMESTAMP() with ORDER BY Clause.

SELECT `contact_id`, `user_id`, `date_backup`, `first_name`, `last_name`
  FROM tbl_contacts WHERE `date_backup` IN (
    SELECT MAX(`date_backup`)
      FROM tbl_contacts WHERE `user_id`= 1 GROUP BY `contact_id`
  )
  ORDER BY UNIX_TIMESTAMP(`date_backup`) DESC, `contact_id`ASC

If require change all date_backup with UNIX_TIMESTAMP(`date_backup`)

jyrkim · Answer 2 · 2014-02-12T13:24:24.940

LIMIT will do the trick in MySQL database (in other databases it would probably be TOP clause). So use LIMIT 10:

SELECT TOP 1 `contact_id`, `user_id`, `date_backup`, `first_name`, `last_name`
FROM tbl_contacts WHERE `date_backup` IN (
SELECT MAX(`date_backup`)
  FROM tbl_contacts WHERE `user_id`= 1 GROUP BY `contact_id`
)
ORDER BY `contact_id`ASC, `date_backup` DESC
LIMIT 10

If you want ten of the most recent ones.

The guide for the LIMIT clause can be found at MySQL reference

Similar kind of issue

score -2 · Answer 3 · answered Feb 12 '14 at 12:56

-2

Just add LIMIT 1 at the end of your query to select only the first line of results.

answered Feb 12 '14 at 12:56

Nick

72
1
2

I want list of data Nick not single line. – Scorpion Feb 12 '14 at 12:57
OK then this has already been answered here http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column – Nick Feb 12 '14 at 13:14

Get most recent date records from mysql

3 Answers3