CODEWITHSUNDEEP
sqlsqlite
Erfan Tavoosi
Erfan Tavoosi

Reputation: 419

How to retrieve the most frequent value of a column for a specific ID in a table

I'm trying to fetch the most frequent value from a SQLite 3 database table for each specific ID (which is the ID of a company). I have tried with GROUP BY and ORDER BY as well as with COUNT() function.

SELECT company_id, max(car) 
FROM car_orders 
GROUP by company_id
ORDER by max(car)

For a specific company_id (9) I am expecting 'Audi' to be in result but this is not the case as its 'Volkswagen' (which is wrong)

Upvotes: 0

Views: 127

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

In the more recent versions of SQLite, you can use window functions:

SELECT cc.*
FROM (SELECT company_id, car, COUNT(*) as cnt,
             ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY COUNT(*) DESC) as seqnum
      FROM car_orders
      GROUP by company_id, car
     ) cc
WHERE seqnum = 1;

In earlier versions, it is a little more complicated:

WITH cc as (
      SELECT company_id, car, COUNT(*) as cnt
      FROM car_orders
      GROUP by company_id, car
     )
SELECT cc.*
FROM cc
WHERE cc.cnt = (SELECT MAX(cc2.cnt)
                FROM cc cc2
                WHERE cc2.company_id = cc.company_id
               );

Upvotes: 1

Parfait
Parfait

Reputation: 107707

Similar to your attempts, consider joining two aggregates that calculates COUNT per car and company and MAX of same counter per company. Below uses CTE introduced in SQLite in version 3.8.3, released in February 2014.

WITH cnt AS ( 
   SELECT company_id, car, COUNT(*) AS car_count
   FROM car_orders 
   GROUP by company_id, car
),
 max_cnt AS (
   SELECT cnt.company_id, MAX(cnt.car_count) as max_count
   FROM cnt
   GROUP BY cnt.company_id
)

SELECT cnt.company_id, cnt.car
FROM cnt
INNER JOIN max_cnt
  ON cnt.company_id = max_cnt.company_id
  AND cnt.car_count = max_cnt.max_count

Upvotes: 1

Related Questions