Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server + Incorrect TOP 1 output

Tags:

sql

sql-server

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   
like image 985
k80sg Avatar asked Dec 28 '22 05:12

k80sg


1 Answers

Select TOP (1) WITH TIES (O.Name) as MostVisited
       ....

WITH TIES provides for "joint top" situations.

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;
like image 108
gbn Avatar answered Jan 09 '23 03:01

gbn