How to get all the records inserted on last date

[sanjeev]

Hi all,

we have the following table structure and data

id user_id date food_description calories_consumed

1 112 2008-11-17 breakfast 1025
2 112 2008-11-17 lunch 4589
3 112 2008-11-17 dinner 3698
4 112 2008-11-18 morning snack 36985
6 112 2008-11-23 Lunch 215
7 112 2008-11-23 Dinner 2541
8 112 2008-11-24 Lunch 2541
9 112 2008-11-24 Dinner 142

we want to display all the records inserted last date i.e. for this example records for 2008-11-24 (2 records)

what the sql query for this, we are using mysql version 4.0.26, thanks for your help.

Regards
Sanjeev.

[r937]

SELECT id
     , user_id
     , `date`
     , food_description
     , calories_consumed
  FROM daTable
 WHERE `date` =
       ( SELECT MAX(`date`)
           FROM daTable )

[r937]

oops, i forgot that you mentioned that you're on 4.0

sorry, you would have to upgrade to 4.1 in order to use a subquery

[sanjeev]

thanks for reply, is there any way to get the results without a sub query?

Thanks
Sanjeev.

[r937]

any way? sure?

run a query to get the max date

take the result and use it in a second query to get the rows for that date

[sanjeev]

Dear Rudy,

any way? sure?

run a query to get the max date

take the result and use it in a second query to get the rows for that date

Yes, I am not getting the MAX date records with the following query

SELECT *, MAX(date) FROM foodjournal WHERE user_id = '112' GROUP BY date

i also try this too

SELECT * , MAX( TO_DAYS( date ) )
FROM foodjournal
WHERE user_id = '112'
GROUP BY TO_DAYS( date )
HAVING TO_DAYS( date ) = MAX( TO_DAYS( date ) )

Its giving me one record per date.

How can i get only the max date records???

Regards
Sanjeev.

[r937]

what i suggested was to run two queries

the first query:

SELECT MAX(`date`) AS max_date
  FROM daTable

grab the value of max_date and use it in the second query:

SELECT id
     , user_id
     , `date`
     , food_description
     , calories_consumed
  FROM daTable
 WHERE `date` = $max_date

simple, eh?