SQL query - How to get max value of a column of each group by column value

Question

I have a table that contains 10 million rows, like this:

I want to group by [CoinNameId] (this column is a foreign key) and get max value of [CreatedAt] for each [CoinNameId] group, but my query returns an error:

How can I solve this?

Answer 1

When you use aggregates in the select clause, every field that is not aggregated needs to be in the group by. That's why you are getting an error. I'm not sure why you had select * in your query.

You'd have to have a query like this:

SELECT CoinNameID, max([CreatedAt])
FROM [dbo].[CoinData]
GROUP BY [CoinNameID]

Answer 2

If you just want column CreatedAt and MAX(CreatedAt) in that case you can do like following.

SELECT CoinNameID, MAX([CreatedAt])
FROM [dbo].[CoinData]
GROUP BY [CoinNameID]

In case if you want all columns along with the MAX([CreatedAt]), you can get it like following.

 SELECT *, 
     (SELECT  MAX([CreatedAt]) 
        FROM [dbo].[CoinData] CDI WHERE CDI.CoinNameID=CD.CoinNameID) AS MAX_CreatedAt
    FROM [dbo].[CoinData] CD