CODEWITHSUNDEEP
sql
Jay
Jay

Reputation: 1039

Group by function with TOP 1 in SQL

I have a table with columns AppId, Comment, TimeStamp. Table can have multiple entries for same AppId. Now what i am trying to do is to get Top 1 Comment for each AppId Order by TimeStamp Desc.

I have tried something like

SELECT TOP 1 Comment, AppId FROM comments GROUP BY AppId

but this doesnt seems to work as i get an error

Column 'comments.Comment' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can someone please point me in right direction on how to get this working

Thanks

Upvotes: 0

Views: 210

Answers (1)

user743382
user743382

Reputation:

Depending on your database engine, you may be able to use ROW_NUMBER().

SELECT Comment, AppId
FROM (
    SELECT Comment, AppId, ROW_NUMBER() OVER (PARTITION BY AppId ORDER BY TimeStamp DESC) AS GroupRowNumber
    FROM comments
) AS sub
WHERE GroupRowNumber = 1

If you don't have that, you need something more hackish:

SELECT Comment, AppId
FROM comments
WHERE TimeStamp = (SELECT MAX(TimeStamp) FROM comments AS comments_inner WHERE comments_inner.AppId = comments.AppId)

Upvotes: 4

Related Questions