• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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
0
fmsol
Asked:
fmsol
  • 3
  • 2
1 Solution
 
Mike EghtebasDatabase and Application DeveloperCommented:
SELECT TOP 3 id, activityType FROM records Grouped By Id, activityType ORDER BY date DESC
0
 
fmsolAuthor Commented:
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
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
SELECT TOP 3 id, activityType, [date] FROM records Grouped By Id, activityType,  [date] ORDER BY [date] DESC
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
fmsolAuthor Commented:
Not there yet; since date is different, it doesn't return distinct activityType
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
re:> since date is different

Could you give more info on this statement? Why and how different?
0
 
SimonCommented:
select top 3 * from (select activityType,max(date)  as LatestDate from records group by activityType)
order by LatestDate DESC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now