SQL Server query Using Bit column in Row_Number Order By

Vote count:

0

I have this SQL Server query that returns all of the group members for each group. But I want to identify if each group member has at least one 'active' device. To do this I am using Row_Number and partitioning on groupID and memberID and ordering by the 'Active' column which is a BIT type. The results of this below query are correct. But I am only using a subset of data. I read that because a bit column is not a number, you should not order by it. It can produce unpredictable results.

How can I cast the BIT column to an INT and use it in the ROW_NUMBER function?

This is the query:

WITH OrderedGroupMembers AS
(
SELECT g.GroupID AS GroupID, gm.RecipientID AS MemberID, r.RecipientName AS MemberName, d.Active AS ActiveDevice,
    ROW_NUMBER() OVER(PARTITION BY g.GroupID, gm.RecipientID ORDER BY d.Active DESC) AS RowNumber
FROM PagingToolGroups g JOIN PagingToolGroupMembers gm ON g.GroupID = gm.GroupID
        JOIN PagingToolRecipients r ON r.RecipientID = gm.RecipientID
        JOIN PagingToolDevices d ON d.RecipientID = gm.RecipientID
WHERE g.GroupCreatorID = 'ge39se35'
)
SELECT GroupID, MemberID, MemberName, ActiveDevice
FROM OrderedGroupMembers
WHERE RowNumber = 1

Thanks.

asked 17 secs ago

Gloria Santin

124

SQL Server query Using Bit column in Row_Number Order By