Geek asks and answers

SQL Server + Incorrect TOP 1 Output

I have a part of my request, as shown below, where TOP 1 should be split between two entries, as there are 2 exits that have the same number of visits. Apparently, the request will return only one record. How can I display the “exact” 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
+4
source share
1 answer
Select TOP (1) WITH TIES (O.Name) as MostVisited
       ....

WITH TIES provides a “top-down” situation.

Example:

DECLARE @t TABLE (foo int, qty int);
INSERt @t VALUES (1, 100), (3, 200), (2, 200);

-- one row, arbitrary
SELECT TOP (1) * FROM @t ORDER BY qty DESC;

-- both rows with "TOP 1 value"
SELECT TOP (1) WITH TIES * FROM @t ORDER BY qty DESC;
+10
source

More articles:


All Articles