Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Unanswered: Select Latest Date in SQL

    Hi,

    I want to select the latest single value based on ProductID. The value should be the last value inserted from the Date column.



    I want to use this as part of a query to delete this value when a button is slected in ASP.Net C#.

    Here is my code which does not work

    select basketID, SessionID, UserID, MAX(Date), productID from ShoppingBasket
    where userID = '1'
    group by productID, basketID, SessionID, UserID

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    When you say, "does not work", what do you actually mean? There are a number of ways to interpret that:
    • The query returns no records
    • The query returns records that are clearly wrong
    • An error message is displayed
    • The query causes your PC to turn into a flock of doves and fly around the room (unlikely, I know, but nothing you've stated so far rules it out)
    • Your SQL instance crashes
    If you include some sample data, expected results and what your query actually returns, someone might be able to help
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Dec 2012
    Posts
    4
    Hello,

    If you want the latest date based solely on ProductID, you should only group by ProductID when aggregating a MAX(). If you group by other columns as well, your query will potentially return multiple dates for the same ProductID in the MAX(Date) column.

    Trim your query down to look like this, and it will only return the absolute latest date for each ProductID:

    select MAX(Date), productID from ShoppingBasket
    where userID = '1'
    group by productID

    If you need to know the basketID, SessionID, and UserID for each of these latest dates, you can join back to ShoppingBasket on MAX(Date) = Date and ProductID = ProductID.

    Joe

Tags for this Thread

Posting Permissions

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