sql - select unique rows based on column by least group count

Question

I need to build a SQL query to select unique records from below table by least number of category count.

eg : simon is falling in to both Red and Green Category but red is having only one record since i should give preference to red.

Name	Category
Simon	Green
Simon	red
James	Green
Mathew	Green

Answer 1

Using analytic functions make this problem tractable:

WITH cte1 AS (
    SELECT *, COUNT(*) OVER (PARTITION BY Category) cat_cnt
    FROM yourTable
),
cte2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY cat_cnt) rn
    FROM cte1
)

SELECT Name, Category
FROM cte2
WHERE rn = 1;

Demo

The first CTE finds the color count across the entire table for each color (regardless of name). The second CTE restricts to the first record per name having the lower color count.

Regarding your using Knex, we could try to rewrite the above without using analytic functions, but it would be very ugly. I might actually suggest just a raw query here.