SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast sanjeev's Avatar
    Join Date
    Sep 2002
    Location
    jaipur - india
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to get all the records inserted on last date

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT id
         , user_id
         , `date`
         , food_description
         , calories_consumed
      FROM daTable
     WHERE `date` =
           ( SELECT MAX(`date`)
               FROM daTable )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast sanjeev's Avatar
    Join Date
    Sep 2002
    Location
    jaipur - india
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for reply, is there any way to get the results without a sub query?

    Thanks
    Sanjeev.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast sanjeev's Avatar
    Join Date
    Sep 2002
    Location
    jaipur - india
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what i suggested was to run two queries

    the first query:
    Code:
    SELECT MAX(`date`) AS max_date
      FROM daTable
    grab the value of max_date and use it in the second query:
    Code:
    SELECT id
         , user_id
         , `date`
         , food_description
         , calories_consumed
      FROM daTable
     WHERE `date` = $max_date
    simple, eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •