Welcome to WebmasterWorld Guest from 107.20.5.156

Forum Moderators: open

Message Too Old, No Replies

Need help with mysql query

mysql query with out duplicating values in two columns

     

LinkSeen

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

1:38 am on Nov 9, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

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

9:51 pm on Nov 9, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

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

2:16 am on Nov 10, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

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

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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month