[SOLVED] Select entire partition where max row in partition is greater than 1

Select entire partition where max row in partition is greater than 1

I'm partitioning by some non unique identifier, but I'm only concerned in the partitions with at least two results. What would be the way to get out all the instances where there's exactly one of the specified identifier?

Query I'm using:

SELECT ROW_NUMBER() OVER
        (PARTITION BY nonUniqueId ORDER BY nonUniqueId, aTimeStamp) as row
    ,nonUniqueId
    ,aTimeStamp
FROM myTable

What I'm getting:

row | nonUniqueId | aTimeStamp
---------------------------------
1   | 1234        | 2014-10-08...
2   | 1234        | 2014-10-09...
1   | 1235        | 2014-10-08...
1   | 1236        | 2014-10-08...
2   | 1236        | 2014-10-09...

What I want:

row | nonUniqueId | aTimeStamp
---------------------------------
1   | 1234        | 2014-10-08...
2   | 1234        | 2014-10-09...
1   | 1236        | 2014-10-08...
2   | 1236        | 2014-10-09...

Thanks for any direction :)

Solution

Based on syntax, I'm assuming this is SQL Server 2005 or higher. My answer will be meant for that.

You have a couple options.

One, use a CTE:

;WITH CTE AS (
    SELECT ROW_NUMBER() OVER
            (PARTITION BY nonUniqueId ORDER BY nonUniqueId, aTimeStamp) as row
        ,nonUniqueId
        ,aTimeStamp
    FROM myTable
)
SELECT *
FROM CTE t
WHERE EXISTS (SELECT 1 FROM CTE WHERE row = 2 and nonUniqueId = t.nonUniqueId);

Or, you can use subqueries:

SELECT ROW_NUMBER() OVER
        (PARTITION BY nonUniqueId ORDER BY nonUniqueId, aTimeStamp) as row
    ,nonUniqueId
    ,aTimeStamp
FROM myTable t
WHERE EXISTS (SELECT 1 FROM myTable 
        WHERE nonUniqueId = t.nonUniqueId GROUP BY nonUniqueId, aTimeStamp HAVING COUNT(*) >= 2);