|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Monday, February 18, 2013 10:38 AM
Points: 53,
Visits: 120
|
|
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
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990,
Visits: 10,540
|
|
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
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Monday, February 18, 2013 10:38 AM
Points: 53,
Visits: 120
|
|
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
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990,
Visits: 10,540
|
|
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
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990,
Visits: 10,540
|
|
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
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Monday, February 18, 2013 10:38 AM
Points: 53,
Visits: 120
|
|
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.
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990,
Visits: 10,540
|
|
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
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Monday, February 18, 2013 10:38 AM
Points: 53,
Visits: 120
|
|
Thanks you very much Paul White NZ. It works
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990,
Visits: 10,540
|
|
|
|
|