select Distinct row from the table

• Ramesh Babu

  SSC Rookie

  Points: 42

  More actions

  July 3, 2002 at 12:06 am

  #78635

  I have modified this table with column EMPNO ,EMPDATE , SHIFT and I have the following data

  Empno EmpDate SHIFT

  1 12/12/2001 3

  1 14/12/2001 1

  2 17/05/2001 4

  3 18/05/2002 1

  I want the following output with the recent empdate and the empno , shift should be distinct .

  SELECT [Empno], Max([EmpDate]),shift

  FROM emp

  GROUP BY [Empno];

  This query is giving me a error :

  and if I use Max(shift):

  Then in Shift instead of 1 , it is showing shift 3 .

  Can any one send me a query for this:

  Empno EmpDate SHIFT

  1 14/12/2001 1

  2 17/05/2001 4

  3 18/05/2002 1

  Thanks in advance

• K. Brian Kelley

  SSC Guru

  Points: 114642

  More actions

  July 3, 2002 at 8:24 am

  #432048

  This is one way:

  
  
  SELECT E.EmpNo, E.EmpDate, E.Shift
  
  FROM Emp E
  
  JOIN (SELECT EmpNo, MAX(EmpDate) EmpDate
  
  FROM Emp
  
  GROUP BY EmpNo) E2 
  
  ON E.EmpNo = E2.EmpNo
  
   AND E.EmpDate = E2.EmpDate
  
  

  K. Brian Kelley

  bkelley@sqlservercentral.com

  http://www.sqlservercentral.com/columnists/bkelley/

  K. Brian Kelley
  @kbriankelley