CODEWITHSUNDEEP
sqlsqlite
SJR59
SJR59

Reputation: 83

SQLite Handle Max() Ties

I have been having trouble returning a query that successfully returns all ties of a max value. The max value comes from an interior query. My sql is below:

SELECT NAME, 
       Max(y.val) 
FROM   (SELECT NAME, 
               Count(*) AS val 
        FROM   numbers N 
        JOIN cards C 
          ON C.mid = N.mid 
        GROUP  BY NAME) AS y

Currently it just returns a single Max value, but I need it to return all if there is a tie. Would anyone be able to help me with this using sqlite?

Upvotes: 1

Views: 1303

Answers (1)

Scott Hunter
Scott Hunter

Reputation: 49803

The idea here is to isolate y and re-use it to both find the max count and then match all the rows with the same count:

WITH y as (SELECT name, COUNT(*) as val 
          FROM Numbers N 
          JOIN Cards C on C.mid = N.mid
          GROUP BY name) 
SELECT name, val 
FROM y 
WHERE (SELECT MAX(val) from y)=val;

Upvotes: 2

Related Questions