CODEWITHSUNDEEP
sqlsql-server
Brad
Brad

Reputation: 1480

Selecting a distinct column with SQL Server

I am attempting to select just 1 distinct column to determine the records that are shown in my query. The column userid is capable of owning several houses which mean userid can be present multiple times currently. However, I only care if they own one house, so I'd like the userid column to be distinct, while the rest of the rows can remain to be whatever is within that row.

Select UserID, House, NumOfPpl, NumOfCars 
from people

Results:

userID  House  NumOfPpl  NumOfCars 
-----------------------------------
1a        red    3          2
1a        blue   1          1
2a        red    3          3
3a        green  4          6
3ab       red    2          1
3ab       blue   2          1

Would need to be:

userID  House  NumOfPpl  NumOfCars
----------------------------------
1a        red    3          2
2a        red    3          3
3a        green  4          6
3ab       red    2          1

It looks like when I try to do a count on userID, I get a value of '1' for each row as it is counting the house aswell.

Select UserID, count(UserId), House, NumOfPpl, NumOfCars 
from people 
group by UserID, House, NumOfPpl, NumOfCars

Upvotes: 1

Views: 76

Answers (1)

dfundako
dfundako

Reputation: 8314

If you must have all columns, select the first record from each partition by userid

 ;with cte AS (
    select userid, 
    house, 
    numofppl, 
    numofcars, 
    row_number() OVER(partition by userID order by house) AS rowcounter
    FROM people
)

SELECT userid, house, numofppl, numofcars
from cte 
WHERE rowcounter = 1

Upvotes: 1

Related Questions