Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Select Row Where Column 1 is Unique

I need help with a select statement.  I would like to have something like the SQL below, except only return values where the Project.JobNumber is unique.  I would like also to take the last ProjectStatus.StatusDate as the tie breaker.  Please let me know how this can be done.  Thanks!
SELECT        Project.JobNumber, ProjectStatus.StatusType, ProjectStatus.StatusDate, ProjectStatus.AssignDate, Project.ID, StatusType.Description
FROM            Project INNER JOIN
                         ProjectStatus ON Project.ID = ProjectStatus.ProjectID INNER JOIN
                         StatusType ON Project.Status = StatusType.ID AND ProjectStatus.StatusType = StatusType.ID
ORDER BY Project.JobNumber, ProjectStatus.StatusDate

Open in new window

0
deloused
Asked:
deloused
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Something like this should work - took the latest status date, but if you change the order by in the OVER clause to ASC then you will get first status date instead.
WITH projectsCTE AS (
	SELECT        Project.JobNumber, ProjectStatus.StatusType, ProjectStatus.StatusDate, ProjectStatus.AssignDate, Project.ID, StatusType.Description,
					row_number() OVER (PARTITION BY Project.JobNumber ORDER BY ProjectStatus.StatusDate DESC) As rNum
	FROM            Project INNER JOIN
                ProjectStatus ON Project.ID = ProjectStatus.ProjectID INNER JOIN
                         StatusType ON Project.Status = StatusType.ID AND ProjectStatus.StatusType = StatusType.ID
)
SELECT JobNumber, StatusType, StatusDate, AssignDate, ID, Description
FROM projectsCTE
WHERE rNum = 1
ORDER BY JobNumber, StatusDate

Open in new window

0
 
delousedAuthor Commented:
Thank you!  Worked great
0
 
Kevin CrossChief Technology OfficerCommented:
You are welcome!
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now