Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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