#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    8
    Rep Power
    0

    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:

    Code:
    SELECT MAX(Date) AS date, Keyword, URL, Ranking, Traffic, Value FROM (SELECT * FROM $Table ORDER BY Ranking ASC) GROUP BY Keyword ORDER BY Value DESC
    I am wondering if i have the syntax all wrong? Any help would be appreciate.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    8
    Rep Power
    0
    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.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    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/...n-columns.html
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    8
    Rep Power
    0
    Originally Posted by r937
    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)
    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?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    8
    Rep Power
    0
    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?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    8
    Rep Power
    0
    This fixed it:

    Code:
    SELECT * FROM (SELECT * FROM (SELECT * FROM $Table ORDER BY DATE DESC , Ranking ASC) AS Rankings GROUP BY Keyword) AS GroupedKW ORDER BY Value DESC
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by ColumK
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    8
    Rep Power
    0
    Originally Posted by r937
    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
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by ColumK
    So do you have a solution?
    would you kindly restate the problem?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo