开发者

SQL Code to Select to select unique column and match with row on another column

Not even how to word this question: I have a table that 开发者_开发问答looks like this:

select * from tblServiceUsers ORDER By ServiceID

0   B88230BB-4943-4C10-AB0E-8ABB88A357A3
0   5653C943-7F9A-4746-B2A3-1160E9204A7E
0   08678EE7-49A7-4F63-BC39-CFCCE2C9AD65
1   B88230BB-4943-4C10-AB0E-8ABB88A357A3
1   5653C943-7F9A-4746-B2A3-1160E9204A7E
2   B88230BB-4943-4C10-AB0E-8ABB88A357A3
2   5653C943-7F9A-4746-B2A3-1160E9204A7E

And I need a query that only returns the top (or first) userid that matches a serviceID. For example, I just want 1 UserID to be displayed for each ServiceID. Such as the following:

0   B88230BB-4943-4C10-AB0E-8ABB88A357A3
1   B88230BB-4943-4C10-AB0E-8ABB88A357A3
2   B88230BB-4943-4C10-AB0E-8ABB88A357A3


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


Why not use TOP 1 and an ORDER BY clause?


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 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜