denissapop denissapop - 3 months ago 14
MySQL Question

How to get latest date from a column in a table in sql?

I'm trying to get the latest date from my OrderItem table using sql, asp.net. However, when i run the program, they show this error:

Incorrect syntax near the keyword 'from'.


Here are my codes:

string strconnect = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection myConnect = new SqlConnection(strconnect);

string command = "
Select OrderItem.ProductID, Product.Name, OrderItem.TotalQty, Product.UnitPrice, OrderItem.TotalPrice
FROM OrderItem
INNER JOIN Product ON OrderItem.ProductID=Product.ProductID
WHERE OrderDate = MAX(OrderDate) from OrderItem";
SqlCommand cmd = new SqlCommand(command, myConnect);

myConnect.Open();

SqlDataReader reader = cmd.ExecuteReader();

GridView1.DataSource = reader;
GridView1.DataBind();
reader.Close();
myConnect.Close();


Please advise!

Answer

You need to remove "from OrderItem" in your WHERE condition since you have specified in your FROM clause.

Select
    OrderItem.ProductID,
    Product.Name,
    OrderItem.TotalQty,
    Product.UnitPrice,
    OrderItem.TotalPrice 
FROM 
    OrderItem 
        INNER JOIN Product 
            ON OrderItem.ProductID=Product.ProductID 
WHERE
    OrderItem.OrderDate = MAX(OrderItem.OrderDate)
Comments