CODEWITHSUNDEEP
c#.netsql
Niko Gamulin
Niko Gamulin

Reputation: 66565

how to select most frequent items from database?

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!

Upvotes: 1

Views: 210

Answers (3)

gbn
gbn

Reputation: 432421

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

Upvotes: 2

Matti Virkkunen
Matti Virkkunen

Reputation: 65156

Would something like this work?

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

Upvotes: 1

dogbane
dogbane

Reputation: 274738

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

Upvotes: 1

Related Questions