Distinct Top n Records with Order By

Question

Distinct Top n Records with Order By

abcim

SSC Eights!

Points: 879
More actions
April 3, 2010 at 4:49 am

#220976

Let say I have a table Employee which have 3 Fields
EmpName
EmpComments
EmpCommentsDate
and Have the following data
Nm CM Date
AA112/12/2009 12:05:00 AM
AA212/12/2009 12:04:00 AM
BB112/12/2009 12:02:00 AM
CC112/12/2009 12:00:00 AM
I want to execute this but getting error,
SELECT DISTINCT TOP (2) EmpName
FROM Employee
ORDER BY EmpCommentsDate

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

It's not exactly clear what output you expect here - especially since there aren't any duplicates in the test data.

The problem, though, is one of logic: you want the top 2 records, in date order, but you also want distinct employee names. Should that return 2 records always?

Anyway, to get the discussion going, here is your test data, with one solution:

DECLARE @Employee

TABLE (

EmpName VARCHAR(50) NOT NULL,

EmpComments VARCHAR(MAX) NULL,

EmpCommentsDate DATETIME NOT NULL

);

INSERT @Employee

(EmpName, EmpComments, EmpCommentsDate)

VALUES ('A', 'A1', '2009-12-12 12:05:00.000');

INSERT @Employee

(EmpName, EmpComments, EmpCommentsDate)

VALUES ('B', 'B1', '2009-12-12 12:02:00.000');

INSERT @Employee

(EmpName, EmpComments, EmpCommentsDate)

VALUES ('C', 'C1', '2009-12-12 12:00:00.000');

SELECT DISTINCT

TOP (2)

EmpName,

EmpCommentsDate

FROM @Employee

ORDER BY

EmpCommentsDate,

EmpName;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

abcim SSC Eights! Points: 879 More actions · Answer 2

Thanks Paul,

I am expecting the output

A

B

Your Query is using Distinct Date which is not desired

I want the Query to Run is

SELECT DISTINCT TOP (2) EmpName

FROM Employee

ORDER BY EmpCommentsDate desc

I want to check Latest Comments from different Users

Paul White SSC Guru Points: 150467 More actions · Answer 3

abcim (4/3/2010)
I want the Query to Run is
SELECT DISTINCT TOP (2) EmpName
FROM Employee
ORDER BY EmpCommentsDate desc
I want to check Latest Comments from different Users

I'm happy to write SQL for you, but we do need to get the rules nailed down 😉

The SQL quoted above is not valid, because it is logically imprecise.

You say you want the latest comments, but that query returns only the employee name :blink:

Your expected output (A & B) also does not include comments.

I imagine what you want is:

The two employees that are associated with the most recent comments added. You always want to see two different employees. Is that correct?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 4

If my guess is correct, try:

SELECT TOP (2)

EmpName,

MAX(EmpCommentsDate)

FROM @Employee

GROUP BY

EmpName

ORDER BY

MAX(EmpCommentsDate) DESC;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

abcim SSC Eights! Points: 879 More actions · Answer 5

The two employees that are associated with the most recent comments added. You always want to see two different employees. Is that correct?

Exactly I want the two different Latest Employee Names.

Paul White SSC Guru Points: 150467 More actions · Answer 6

abcim (4/3/2010)
The two employees that are associated with the most recent comments added. You always want to see two different employees. Is that correct?
Exactly I want the two different Latest Employee Names.

Oh good. Try the last query I posted then, hopefully that will work for you?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

abcim SSC Eights! Points: 879 More actions · Answer 7

abcim

SSC Eights!

Points: 879

April 3, 2010 at 7:27 am

#1145313

Thanks you very much Paul White NZ.

It works

Paul White SSC Guru Points: 150467 More actions · Answer 8

Thanks for the feedback.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi