I have a portion of my query as shown below which the TOP 1 is supposed to be shared among 2 records as there are 2 outlets that has the same visit count. Apparently, the query will only return one record. How can I display the "accurate" output in this case?
Select TOP 1 (O.Name) as MostVisited
From [Trans] T
INNER JOIN [Outlet] O
On (T.Outlet_Code = O.Code)
Where [VoidBy] IS NULL AND [VoidOn] IS NULL AND CardNo In
(Select [CardNo] From [Card] Where [CardNo] = 'CARDX' AND [MemberID] = @MemberId)
Group by O.Name
Order by Count(T.Outlet_Code) Desc
WITH TIES provides for "joint top" situations.
Example: