Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Distinct Top n Records with Order By Expand / Collapse
Author
Message
Posted Saturday, April 3, 2010 4:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:00 AM
Points: 53, Visits: 125
Let say I have a table Employee which have 3 Fields
EmpName
EmpComments
EmpCommentsDate
and Have the following data

Nm CM Date
A A1 12/12/2009 12:05:00 AM
A A2 12/12/2009 12:04:00 AM
B B1 12/12/2009 12:02:00 AM
C C1 12/12/2009 12:00:00 AM

I want to execute this but getting error,

SELECT DISTINCT TOP (2) EmpName
FROM Employee
ORDER BY EmpCommentsDate
Post #896202
Posted Saturday, April 3, 2010 5:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:07 PM
Points: 11,194, Visits: 11,109
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896203
Posted Saturday, April 3, 2010 5:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:00 AM
Points: 53, Visits: 125
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
Post #896208
Posted Saturday, April 3, 2010 6:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:07 PM
Points: 11,194, Visits: 11,109
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
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896212
Posted Saturday, April 3, 2010 6:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:07 PM
Points: 11,194, Visits: 11,109
If my guess is correct, try:

SELECT  TOP (2)
EmpName,
MAX(EmpCommentsDate)
FROM @Employee
GROUP BY
EmpName
ORDER BY
MAX(EmpCommentsDate) DESC;





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896213
Posted Saturday, April 3, 2010 6:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:00 AM
Points: 53, Visits: 125
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.
Post #896214
Posted Saturday, April 3, 2010 6:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:07 PM
Points: 11,194, Visits: 11,109
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896219
Posted Saturday, April 3, 2010 7:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:00 AM
Points: 53, Visits: 125
Thanks you very much Paul White NZ.
It works
Post #896236
Posted Saturday, April 3, 2010 7:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:07 PM
Points: 11,194, Visits: 11,109
Thanks for the feedback.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896238
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse