Solved

SELECT TOP 3 and DISTINCT

Posted on 2014-12-07
6
165 Views
Last Modified: 2014-12-07
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
Comment
Question by:fmsol
  • 3
  • 2
6 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40485711
SELECT TOP 3 id, activityType FROM records Grouped By Id, activityType ORDER BY date DESC
0
 

Author Comment

by:fmsol
ID: 40485728
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40485730
SELECT TOP 3 id, activityType, [date] FROM records Grouped By Id, activityType,  [date] ORDER BY [date] DESC
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:fmsol
ID: 40485732
Not there yet; since date is different, it doesn't return distinct activityType
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40485739
re:> since date is different

Could you give more info on this statement? Why and how different?
0
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40485744
select top 3 * from (select activityType,max(date)  as LatestDate from records group by activityType)
order by LatestDate DESC
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question