Hate GROUP BY

[pat15]

I have a Query with three fields
StockId
Date
OrderId


I want to Max(Date) and Group StockId and get the OrderId

However, it is required to do GROUP BY OrderId in SQL and this will
return every record.

I just need that OrderId, and this thing won't let me.

By the way, StockIds have duplicated OrderId.

 

[Pat Hartman]

You need to either use a subquery or nest two queries.

query1:

Select StockID, Max(OrderDate) As MaxOrderDate
From YourTable
Group by StockID;

query2:
Select YourTable.StockID, YourTable.OrderDate, YourTable.OrderID
From YourTable InnerJoin query1 on YourTable.StockID = query1.StockID AND YourTable.OrderDate = query1.MaxOrderDate;

 

[pat15]

I thought SQLs were easy and clean,

but today I found out it can get messy.

anyway, Thank you for your help

 

[pat15]

Just in case,

What if there are several records with same OrderDate?

 

[Pat Hartman]

Then the query will return several records. If you want only one record and you don't care which one, you need to change query2 to a totals query also but in this case you would use the First() function for all fields rather than Group By or Max().

 

[pat15]

Thank you