[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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