Selecting top distinct values with group by clause

CrimsonBane

Hi, I got an sql situation I have data like this:

select * from data;

DATA_HASH OPT_HASH TOTAL_QUERYS

228AB820 00000000 1
228AB820 00000000 1
0A8C0D24 97B17E23 1
0A8C0D24 97B17E23 1
0A8C0D24 97B17E23 1
0A8C0D24 97B17E23 1
0A8C0D24 97B17E23 1
38BA6FA6 97B17E23 1
38BA6FA6 97B17E23 1
38BA6FA6 97B17E23 1
FD852A75 97B17E23 1
FD852A75 97B17E23 1
FD852A75 97B17E23 1
FD852A75 97B17E23 1
FD852A75 97B17E23 1
FD852A75 97B17E23 1
FD852A75 97B17E23 1
FD852A75 97B17E23 1
FD852A75 97B17E23 1
FD852A75 97B17E23 1
FD852A75 97B17E23 1
FD852A75 97B17E23 1
0A8C0D24 D8F0E8E4 1

23 record(s) selected.

Now what I need to do is to group the data and count the number of occourences where data_hash and options_hash occour.

That is done by this statement:

select data_hash,opt_hash,SUM(total_querys) as total FROM data GROUP BY opt_hash,data_hash ORDER BY total DESC

DATA_HASH OPT_HASH TOTAL

FD852A75 97B17E23 12
0A8C0D24 97B17E23 5
38BA6FA6 97B17E23 3
228AB820 00000000 2
0A8C0D24 D8F0E8E4 1

5 record(s) selected.

Now here is the problem I only want the highest occource of OPT_HASH per DATA_HASH so the output I want would be

DATA_HASH OPT_HASH TOTAL

FD852A75 97B17E23 12
0A8C0D24 97B17E23 5
38BA6FA6 97B17E23 3
228AB820 00000000 2

Now you say well do it in php, well the problem is this is a subselect query so I need to get it to return the correct results.

Any help is greatly appreciated!!!

mystrymaster

just a quess but what about a max DISTINCT?

CrimsonBane

I wish it were that easy becasue it is over two columns I don't think you can use the distinct clause over two columns yet alone how do you integrate a max aggragte function into the query to work with distinct.

mystrymaster

yeah makes sense now that it is thought about a little more - Sorry knee deep in my own issue and just a thought taht popped in the head.

Good Luck

steadyguy

If you can do it in SQL, don't do it in PHP, is what I say 😃
If I understand you correctly, you can just add a LIMIT clause. For example, suppose you only want the top two totals:

SELECT data_hash, opt_hash, SUM(total_querys) AS total
FROM data
GROUP BY opt_hash, data_hash
ORDER BY total DESC
LIMIT 2