Multiple Select Statements to Order Data

Good Morning,

New to the forums, I am having a spot of trouble with some code I am writing. First let me give you a breakdown.

I am creating a Ranking module for one of the companies websites. These will show the historical rankings of the keywords so I first order it by date. The problem with this is that as there could be multiple urls ranking for that specific keyword I am then trying to order it by rank first before grouping by keyword (to obtain the highest ranking page to be displayed first).

My code all works except for when i try to order it by ranking first by using a second select query:



I am wondering if i have the syntax all wrong? Any help would be appreciate.

Figured out the problem. I had to specify that it was in fact a new table by putting as tablename:

SELECT MAX(Date) AS date, Keyword, URL, Ranking, Traffic, Value FROM (SELECT * FROM $Table ORDER BY Ranking ASC) as rankingordered GROUP BY Keyword ORDER BY Value DESC

Hope that helps someone who had the same problem.

what you have coded, will only work in mysql

therefore let's move this thread to the mysql forum

now the bad news: the URL that you display beside the MAX(date) is not necessarily the URL that is actually on the same row as the MAX(date)

read this: http://dev.mysql.com/doc/refman/5.0...en-columns.html

Yes I have come to realize this. The data is looking fairly skewed. Could I ask if anyone has come up with a solution to this, or perhaps there is a better way to organize the data?

Would doing something like:

SELECT FROM (SELECT FROM $table Order By Date ASC And Ranking DESC) Group By Keyword

Work? i.e. does the keyword now group to the first instance of that keyword as it is now ordered by date and then ordered by rankings?

This fixed it:

no, sorry, it might look fixed, but i assure you, when you have "hidden" columns in the SELECT clause (i.e. columns that aren't also in the GROUP BY clause, as explained by that link i gave you), you are not guaranteed to get the results you think you're getting

So do you have a solution? Because trial running 100 keywords seems to output the correct data :S

would you kindly restate the problem?