CODEWITHSUNDEEP
sqlsql-server-2008
Patrick Kerr
Patrick Kerr

Reputation: 23

Getting Rid of the Older Record

There is a result set where all the records are unique columns like this . I only want to display the records with newer dates if there is another record which belongs to the same BusinessID. My query to get this result is 

SELECT 
  FirstName, 
  LastName,
  Department,
  StartDate,
  Title,
  PhoneNumber,
  BusinessEntityID
FROM
  (SELECT
     E.BusinessEntityID,
     H.FirstName,
     H.LastName,
     H.Title,
     H.Department,
     E.PhoneNumber,
     E.CountryRegionName,
     E.PostalCode,
     H.StartDate
   FROM 
     CS120Exam_EmployeeDepartmentHistory H
   JOIN 
     CS120Exam_Employee E
   ON 
     E.BusinessEntityID=H.BusinessEntityID ) x 
   ORDER BY BusinessEntityID

And the result is

BusinessID  FirstName LastName Department StartDate
----------- --------- -------- ---------- ----------
1           aaa       mate     staff      2002-02-02 <----- DO NOT want this 
1           aaa       mate     admin      2004-03-05
2           john      mate     admin      2001-03-06
3           sun       kent     admin      2004-03-05
4           bbb       clark    staff      2006-02-02 <----- DO NOT want this 
4           bbb       clark    admin      2009-03-05

Upvotes: 1

Views: 59

Answers (4)

user330315
user330315

Reputation: 

SELECT 
  FirstName, 
  LastName,
  Department,
  StartDate,
  Title,
  PhoneNumber,
  BusinessEntityID
FROM
  (SELECT
     E.BusinessEntityID,
     H.FirstName,
     H.LastName,
     H.Title,
     H.Department,
     E.PhoneNumber,
     E.CountryRegionName,
     E.PostalCode,
     H.StartDate,
     row_number() over (partition by BusinessID order by StartDate desc) as rn
   FROM 
     CS120Exam_EmployeeDepartmentHistory H
   JOIN 
     CS120Exam_Employee E
   ON 
     E.BusinessEntityID=H.BusinessEntityID ) x 
WHERE rn = 1
ORDER BY BusinessEntityID

Might be faster than using a sub-select to get the max. startdate because only a single scan over the table is required.

Upvotes: 0

Ken White
Ken White

Reputation: 125727

You need a sub-select to find the latest date for each BusinessEntityID. Try this (untested, of course):

SELECT 
  FirstName, 
  LastName,
  Department,
  StartDate,
  Title,
  PhoneNumber,
  BusinessEntityID
FROM
  (SELECT
     E.BusinessEntityID,
     H.FirstName,
     H.LastName,
     H.Title,
     H.Department,
     E.PhoneNumber,
     E.CountryRegionName,
     E.PostalCode,
     H.StartDate
   FROM 
     CS120Exam_EmployeeDepartmentHistory H
   JOIN 
     CS120Exam_Employee E
   ON 
     E.BusinessEntityID=H.BusinessEntityID ) x 
   WHERE x.StartDate = 
     (SELECT 
        Max(cs.StartDate) 
      FROM
        CS120Exam_EmployeeDepartmentHistory cs
      WHERE
        cs.BusinessEntityID = x.BusinessEntityID)
   ORDER BY BusinessEntityID

Upvotes: 1

Kirill Bestemyanov
Kirill Bestemyanov

Reputation: 11964

You can use GROUP BY and max(startdate)

SELECT 
  FirstName,
  LastName,
  Department,
  max(StartDate),
  Title,
  PhoneNumber,
  BusinessEntityID
FROM
  (SELECT 
     E.BusinessEntityID,
     H.FirstName,
     H.LastName,
     H.Title,
     H.Department,
     E.PhoneNumber,
     E.CountryRegionName,
     E.PostalCode,
     H.StartDate
  FROM 
    CS120Exam_EmployeeDepartmentHistory H
  JOIN 
    CS120Exam_Employee E
  ON 
   E.BusinessEntityID=H.BusinessEntityID ) x 
  ORDER BY 
    BusinessEntityID
  GROUP BY 
    FirstName,
    LastName,
    Department,
    Title,
    PhoneNumber,
    BusinessEntityID

Upvotes: 0

ajp
ajp

Reputation: 1490

I am thinking of grouping by rest of columns and getting maximum of date. In this case you will get a single record with max date

SELECT FirstName,LastName,Department,max(StartDate),Title,PhoneNumber,BusinessEntityID
FROM
(SELECT E.BusinessEntityID,H.FirstName,H.LastName,H.Title,H.Department,E.PhoneNumber,
E.CountryRegionName,E.PostalCode,H.StartDate
FROM CS120Exam_EmployeeDepartmentHistory H
JOIN CS120Exam_Employee E
ON E.BusinessEntityID=H.BusinessEntityID )x 
group by  FirstName,LastName,Department,Title,PhoneNumber,BusinessEntityID
ORDER BY BusinessEntityID

Upvotes: 0

Related Questions