Why not use TOP 1 and an ORDER BY clause?
Brad Heller
2010-06-21 16:10:51
views:
26answers:
3top 1 will only return 1 row. I have columns ServiceID, and UserID. There are some ServiceID's that have up to 5 userid's, but I only want to display one userid per unique serviceid.
Timothy S
2010-06-21 16:16:30
+1
A:
You don't say which version of SQL server it is, but for MS SQL Server 2005 onwards you can do something like :
Select SecID, ServiceID, UserID from
(
select ROW_NUMBER() OVER (PARTITION BY ServiceID ORDER BY Secid) AS row_number,
SecID,
ServiceID,
UserID
From tblSecServiceUsers
)
tempTable
where row_number = 1
sgmoore
2010-06-21 16:27:01
A:
You sound like you're not too bothered which UserId you just want any one? If so this would work.
SELECT MAX(UserID) AS UserID, ServiceID
FROM tblServiceUsers
GROUP BY ServiceID
ORDER BY ServiceID
Martin Smith
2010-06-21 16:33:07