CODEWITHSUNDEEP
sqlpostgresql
TheRealFakeNews
TheRealFakeNews

Reputation: 8143

SQL: How to get array of IDs from most common values in a table?

I have the most counts of each value in a column like so:

SELECT col, COUNT(col)
FROM table
GROUP BY col
ORDER BY col DESC;

But I would like to add another column where the IDs of the records with those values are in an array.

For example, if "blueberry" was the most common value, the cell next to it should show the ids of those records, e.g. - [1, 21, 123]

Upvotes: 0

Views: 2037

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can get an array for each col:

SELECT col, COUNT(*), ARRAY_AGG(id) as ids
FROM table
GROUP BY col
ORDER BY COUNT(*) DESC;

You can fetch the first row of the above query:

SELECT col, COUNT(*), ARRAY_AGG(id) as ids
FROM table
GROUP BY col
ORDER BY COUNT(*) DESC
FETCH FIRST 1 ROW ONLY;

Does this do what you want?

Upvotes: 1

Related Questions