CODEWITHSUNDEEP
sqlsql-server
Luan Tran
Luan Tran

Reputation: 414

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

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

enter image description here

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:

enter image description here

How can I solve this?

Upvotes: 3

Views: 296

Answers (3)

Prabhath Amaradasa
Prabhath Amaradasa

Reputation: 503

You have select * on your query 

SELECT
     CoinNameId,MAX(CreatedAt) AS MaxCreatedAt
     FROM [dbo].[CoinData]
     GROUP BY CoinNameId

This will return MAX(CreatedAt) with other columns

 SELECT
    *, MAX([CreatedAt]) OVER (PARTITION BY [CoinNameId])
FROM [dbo].[CoinData]

Upvotes: 0

PSK
PSK

Reputation: 17953

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

Upvotes: 1

kaineub
kaineub

Reputation: 162

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]

Upvotes: 1

Related Questions