[RESOLVED] SQL DISTINCT – Select entire row, with one distinct column.

On September 3, 2011, in How-to, Scripting, by Cubert aka (Cube Dweller)

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.



Tagged with:

Leave a Reply


%d bloggers like this: