homepage Welcome to WebmasterWorld Guest from 54.166.108.167
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved