Using MSSQL to define a distinct column and then return all rows.
This was more than a simple query I came to find out. To do this we use the Row_Number() function and then we select the rows with number 1. Simple right?
Here was my data before we used the Row_Number function.
The RED Arrow is the row I needed to be Distinct on. The Blue Arrow repersented the uniqueness of the records.
1 project with 3 different managers that have been assigned to that project. I just wanted all unique projects with a status of ‘open’. Not the same project 3 time in my results.
When you use DISTINCT it only wants to return the column that is being DISTINCT, so how do we return all columns while only being DISTINCT on 1 column?
Here is how:
;WITH myNewTable AS ( SELECT TOP 1000 [manager], [pm_project_recid], [project_id], [company_recid], [company_name], [pm_status_recid], [status] , [closed_flag], ROW_Number() OVER (PARTITION BY project_id ORDER BY project_id) AS RowNumber FROM [v_cbi_ProjectOverall])
SELECT * FROM myNewTable WHERE RowNumber = 1 and status = ‘open’
This will return unique rows based on the DISTINCT column [project_id] and the return will look something like this:
I hope this hels someone out there.