fmsol
asked on
SELECT TOP 3 and DISTINCT
I have a table "records": id (identity), date (datetime), activityType (integer) with several entries.
I want the three latest activityType, records sorted by date:
Something like:
SELECT TOP 3 DISTINCT activityType FROM records ORDER BY date DESC
I want the three latest activityType, records sorted by date:
Something like:
SELECT TOP 3 DISTINCT activityType FROM records ORDER BY date DESC
Mike Eghtebas
SELECT TOP 3 id, activityType FROM records Grouped By Id, activityType ORDER BY date DESC
ASKER
SELECT TOP 3 id, activityType FROM records Group By Id, activityType ORDER BY date DESC
returns:
Column "date" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause
returns:
Column "date" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause
SELECT TOP 3 id, activityType, [date] FROM records Grouped By Id, activityType, [date] ORDER BY [date] DESC
ASKER
Not there yet; since date is different, it doesn't return distinct activityType
re:> since date is different
Could you give more info on this statement? Why and how different?
Could you give more info on this statement? Why and how different?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.