Step one would be to identify which catagories are the highest scoring ones

SELECT catagoryID, max( score ) AS score
FROM (
SELECT catagoryID1 AS catagoryID, score
FROM `test`
UNION
SELECT catagoryID2 AS catagoryID, score
FROM `test`
)Combined
GROUP BY catagoryID
ORDER BY score DESC
LIMIT 10
GROUP BY catagoryID
ORDER BY score desc

Then you would need to join that back to the table to get the ID.

SELECT catagoryID, score, (

SELECT ID
FROM test
WHERE test.score = ranked.score
AND (
test.catagoryID1 = ranked.catagoryID
OR test.catagoryID2 = ranked.catagoryID
)
LIMIT 1
) AS ID
FROM (

SELECT catagoryID, max( score ) AS score
FROM (

SELECT catagoryID1 AS catagoryID, score
FROM test
UNION
SELECT catagoryID2 AS catagoryID, score
FROM test
)Combined
GROUP BY catagoryID
ORDER BY score DESC
LIMIT 10
)ranked


P.S. You will have to change test to your table name.

Wow!

That is far more complicated than I've ever done before. I never would have figured this out. In fact I'm still at a bit of a loss at what you are doing with all the nested commands and the syntax of it all. Unfortunately I am getting Syntax errors when I try to implement it. near the following code

GROUP BY catagoryID
ORDER BY score DESC

Can you post your SQL here? I just reran the above and it works fine.

Why do you have two categories and one score? That's what complicates the issue.

You should be able to run bits on it own.
Start with.

SELECT catagoryID1 AS catagoryID, score
FROM test
UNION
SELECT catagoryID2 AS catagoryID, score
FROM test

Thanks for your help on this.

This is my query

$query = "SELECT NicheID, max( TotalScore ) AS TotalScore
FROM (
SELECT MainNicheID AS NicheID, TotalScore
FROM site
UNION
SELECT SubNicheID AS NicheID, TotalScore
FROM site
)Combined
GROUP BY NicheID
ORDER BY TotalScore DESC
LIMIT 10
GROUP BY NicheID
ORDER BY TotalScore desc

SELECT NicheID, score, (

SELECT SiteID
FROM site
WHERE site.TotalScore = ranked.TotalScore
AND (
site.MainNicheID = ranked.NicheID
OR site.SubNicheID = ranked.NicheID
)
LIMIT 1
) AS SiteID
FROM (

SELECT NicheID, max( TotalScore ) AS TotalScore
FROM (

SELECT MainNicheID AS NicheID, TotalScore
FROM site
UNION
SELECT SubNicheID AS NicheID, TotalScore
FROM site
)Combined
GROUP BY NicheID
ORDER BY TotalScore DESC
LIMIT 10
)ranked ";

Two issues, you had both my original query, which I showed as an example, and then also had the second query (which has it embedded in the second one).
Also you had one other error (score vs TotalScore).

SELECT NicheID, TotalScore, (

SELECT SiteID
FROM site
WHERE site.TotalScore = ranked.TotalScore
AND (
site.MainNicheID = ranked.NicheID
OR site.SubNicheID = ranked.NicheID
)
LIMIT 1
) AS SiteID
FROM (

SELECT NicheID, max( TotalScore ) AS TotalScore
FROM (

SELECT MainNicheID AS NicheID, TotalScore
FROM site
UNION
SELECT SubNicheID AS NicheID, TotalScore
FROM site
)Combined
GROUP BY NicheID
ORDER BY TotalScore DESC
LIMIT 10
)ranked

It makes a lot more sense now that I know it is not all one query. It runs, but it is producing duplicate siteID and NicheID results.

I tried the simplified version below but I am getting duplicate NicheID.

SELECT SiteID FROM
( SELECT SiteID, TotalScore, MainNicheID as NicheID FROM site
UNION
SELECT SiteID, TotalScore, SubNicheID as NicheID FROM site
ORDER BY TotalScore DESC, NicheID
) Combined
GROUP BY SiteID
ORDER BY TotalScore DESC, NicheID
LIMIT 10

I appreciate your help on this. I have spent 3 days on this and its driving me nuts!

I gave up on the UNION and got results I can live with just using the MainNicheID.