| Need help with mysql query
mysql query with out duplicating values in two columns |
LinkSeen
msg:4228275 | 1:05 am on Nov 9, 2010 (gmt 0) |
I have a MYSQL table with the following columns:
ID, catagoryID1, catagoryID2, score
catagoryID1 and catagoryID2 are keys referencing the same "catagory" table.
I need a query that will give me the row ID of the 10 highest score values, ordered by score values but not duplicating a categoryID whether it is from categoryID1 or categoryID2.
I have spent way to much time on this and am still baffled.
|
Dijkgraaf
msg:4228281 | 1:38 am on Nov 9, 2010 (gmt 0) |
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.
|
LinkSeen
msg:4228295 | 3:09 am on Nov 9, 2010 (gmt 0) |
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
|
Dijkgraaf
msg:4228625 | 9:51 pm on Nov 9, 2010 (gmt 0) |
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
|
LinkSeen
msg:4228663 | 1:28 am on Nov 10, 2010 (gmt 0) |
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 ";
|
Dijkgraaf
msg:4228684 | 2:16 am on Nov 10, 2010 (gmt 0) |
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
|
LinkSeen
msg:4228993 | 11:03 pm on Nov 10, 2010 (gmt 0) |
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!
|
LinkSeen
msg:4229320 | 8:03 pm on Nov 11, 2010 (gmt 0) |
I gave up on the UNION and got results I can live with just using the MainNicheID.
|
|
|
