CODEWITHSUNDEEP
phpmysqlsql
Suleman Ahmad
Suleman Ahmad

Reputation: 2113

SQL Query That Should Return Least two days record

I have a table "abc" where i store timestamp having multiple records let suppose 

1334034000  Date:10-April-2012
1334126289  Date:11-April-2012 
1334291399  Date:13-April-2012

I want to build a sql query where I can find at first attempt the records having last two day values and so second time the next two days . . .

Example:

Select *,dayofmonth(FROM_UNIXTIME(i_created)) from notes
where dayofmonth(FROM_UNIXTIME(i_created)) > dayofmonth(FROM_UNIXTIME(i_created)) -2
order by dayofmonth(FROM_UNIXTIME(i_created))

this query returns all the records date wise but we need very most two days record.

Please suggest accordingly. Thanks in advance

Edited:

If today is 13 April and our table has max day 10, 9, 8, . . . april then we have to show the 10, 9 April and his last date records

Upvotes: 0

Views: 331

Answers (2)

Devart
Devart

Reputation: 122002

Try this query -

SELECT t1.* FROM table t1
  JOIN (SELECT date FROM table GROUP BY date DESC LIMIT 2) t2
    ON t1.date = t2.date

It will show all records with date = '11-April-2012' and '13-April-2012'.

Is this what you want?

Edited:

SELECT
  t1.*, t2.date
FROM
  notes t1
JOIN (SELECT
        DATE(FROM_UNIXTIME(i_created)) date
      FROM
        notes
      GROUP BY
        date DESC
      LIMIT
        2
  ) t2
ON
  DATE(FROM_UNIXTIME(t1.i_created)) = t2.date

Upvotes: 2

kjones
kjones

Reputation: 1423

Add limit 2 to the end of your query

Upvotes: 0

Related Questions