how to select most frequent items from database?

Question

Hi,

In the database there are column values which appear in multiple rows. The example rows are the following (in csv format):

    AFAB19476C2CEEEE101FFA45FD207BA8B6185B29,539EE0643AFC3A3BE3D20DC6BE7D5376DC536D34,9800,58,29,24,34,2
A801DA9B2F4116A7A1B14A13532B2177C7436C43,91850E4C50536D45C9CEAFE5FB5B3A87154EB754,9800,15,15,15,15,1
4C1E0B5387FB7FE19FC1ED682D0EB08249779180,9B17AE806C79437945F99C054B59A859D5639D11,9800,51,51,51,51,1
5B83A4BE4161497C62471BF133A4E1AD905D25F8,BFF4CED4F54F221A76714B311623398070847B26,9800,71,71,71,71,1
145145E49302ABBEEFF2797CAA8E122FFD3D5BFD,0C287F08E8E11DB4CF10CEB5801EBD61E7664FE4,9800,55,55,55,55,1
99C1F96461BC870574D002034F001BA3F96A9AB5,2EC4F3158764DC07D981008B3054B97809A0B048,Tujina,34,34,34,34,1
**CCB433630C735A8DA1B7828C10820B8CF91F25B9**,2C9C297BEF9CC1C0CF16A0559DE828FA0E226698,9817,339,169,137,202,2
BF2A7F0A9AD762B46A4423F76BF0479B9A72F163,336FB392EA4EF85EFE2563332CDE7D32FCE711B2,9800,34,34,34,34,1
...
**CCB433630C735A8DA1B7828C10820B8CF91F25B9**,C4015FE337F1EEFA1ECE4143D77F9627BEB9D358,9800,464,464,464,464,1
**CCB433630C735A8DA1B7828C10820B8CF91F25B9**,0EC08D78C637EF0A05E858B2BAC85C3EF05DF959,9800,73,73,73,73,1

In this example the value CCB433630C735A8DA1B7828C10820B8CF91F25B9 appears in three rows in combination with different values.

I am looking for a way to count in how many rows the value from the 1st column appears and then order the values by the number of rows that contain that value.

For example only the value from the first row was checked the query would be the following

SELECT COUNT (*) FROM records WHERE column_1 = 'AFAB19476C2CEEEE101FFA45FD207BA8B6185B29'

but instead of just first row the values from all rows have to be checked.

I would be very thankful if anyone of you could suggest an appropriate SQL query statement or a function to sort all the values from the first column by the number of repeatings.

Thank you!

Answer 1

The following will show you the counts of all the values in column1, ordered in ascending order:

select column_1, count(*)
from records
group by column_1
order by 2 asc

Answer 2

Would something like this work?

SELECT column_1, COUNT(column_2)
FROM records
GROUP BY column_1
ORDER BY COUNT(column_2) DESC

Answer 3

SELECT column_1, COUNT(*)
FROM records
GROUP BY column_1
ORDER BY COUNT(*) DESC