SitePoint Sponsor

User Tag List

Results 1 to 21 of 21

Thread: [MySQL] Query

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question [MySQL] Query

    Hello.

    I am working with a website with statistics to online game.
    During this time , I've got some problems.

    Could anyone look at this query?
    I would like to get output with a records from last day in database.
    This query \/ showing all the records from a table.(like 5k records)
    PHP Code:
    SELECT olympa.nameolympa.experienceolympa_gained.gained 
        FROM olympa
    olympa_gained 
        WHERE 
    (olympa.rank olympa_gained.rank AND olympa_gained.date =(SELECT MAX(datefrom olympa_gained)) 
    I need something in this kind :

    PHP Code:
    SELECT olympa.nameolympa.experienceolympa_gained.gained 
        FROM olympa
    olympa_gained 
        WHERE 
    ( IF(olympa_gained.date =(SELECT MAX(datefrom olympa_gainedTHEN olympa.rank olympa_gained.rank  
    because atm it assign all the records from (olympa.rank = olympa_gained.rank)

    Any help apreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT olympa.name
         , olympa.experience
         , olympa_gained.gained   
      FROM olympa
    INNER
      JOIN ( SELECT rank
                  , MAX(date) AS latest_date
               FROM olympa_gained
             GROUP
                 BY rank ) AS m
        ON m.rank = olympa.rank 
    INNER
      JOIN olympa_gained   
        ON olympa_gained.rank = olympa.rank 
       AND olympa_gained.date = m.latest_date
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT olympa.name
         , olympa.experience
         , olympa_gained.gained   
      FROM olympa
    INNER
      JOIN ( SELECT rank
                  , MAX(date) AS latest_date
               FROM olympa_gained
             GROUP
                 BY rank ) AS m
        ON m.rank = olympa.rank 
    INNER
      JOIN olympa_gained   
        ON olympa_gained.rank = olympa.rank 
       AND olympa_gained.date = m.latest_date
    I am getting same results with this code like with this one upper.
    MySQL gave an output with 3,3k rows ( I Need only 300 rows ) every 300 rows have another date of a day.

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    e.g
    Table: olympa_gained


    table olympa_gained have a 3,3k records and each 300 rows have repeatedly rank from 1-300 , it's why I need to select records with MAX(date).


    Any Solution?

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Problem solved.
    Thanks for your code above , that's helped after a few amendments.

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's selecting first 300 rows instead of 300 with current date. [ all the code except a bold font statement ]

    Is there possibility to add a WHERE STATEMENT before inner join?

    Code:
    SELECT olympa.name
    , olympa.rank
    	, olympa.experience
    	, olympa_gained.gained
    	, olympa_weekly.weekly
      , MAX( olympa.date ) as date
    	FROM olympa           
    ----- WHERE date = (SELECT MAX( date ) from Olympa)------
    INNER
    JOIN (
    	SELECT rank
    	, MAX( date ) AS latest_date
    	FROM olympa_gained
    	  GROUP
    		BY rank
    	  ) AS m ON m.rank = olympa.rank
    INNER
    JOIN (
    	SELECT rank
    	, MAX( date ) AS wlatest_date
    	FROM olympa_weekly
    	  GROUP
    		BY rank
    	  ) AS n ON n.rank = olympa.rank
    INNER
    JOIN olympa_gained
    ON olympa_gained.rank = olympa.rank AND olympa_gained.date = m.latest_date
    INNER
    JOIN olympa_weekly
    ON olympa_weekly.rank = olympa.rank AND olympa_weekly.date = n.wlatest_date
    
    GROUP BY olympa_gained.rank ORDER BY olympa.experience DESC

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    no

  8. #8
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is there another way to do it?

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    try putting it before the last GROUP BY

  10. #10
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    try putting it before the last GROUP BY
    Thanks.

  11. #11
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a 2 queries.

    This one took ( 300 rows fetched in 0,0035s(0,6423s) )
    Code:
    SELECT olympa.rank
    , olympa.name
      , olympa.level
    	, olympa.experience
    	, olympa_gained.gained
    	, olympa_weekly.weekly
      , MAX( olympa.date ) as date
    	FROM olympa
    INNER
    JOIN (
    	SELECT rank
    	, MAX( date ) AS latest_date
    	FROM olympa_gained
    	  GROUP
    		BY rank
    	  ) AS m ON m.rank = olympa.rank
    INNER
    JOIN (
    	SELECT rank
    	, MAX( date ) AS wlatest_date
    	FROM olympa_weekly
    	  GROUP
    		BY rank
    	  ) AS n ON n.rank = olympa.rank
    
    INNER
    JOIN olympa_gained
    ON olympa_gained.rank = olympa.rank AND olympa_gained.date = m.latest_date
    INNER
    JOIN olympa_weekly
    ON olympa_weekly.rank = olympa.rank AND olympa_weekly.date = n.wlatest_date
    WHERE olympa.date = m.latest_date
    GROUP BY olympa_gained.rank ORDER BY olympa.experience DESC

    Just added - olympa_voc.voc and this took much more , more than 3 minutes ... 300 rows fetched in 0,0340s (181,2258s)

    Code:
    SELECT olympa.rank
    , olympa.name
      , olympa_voc.voc
      , olympa.level
    	, olympa.experience
    	, olympa_gained.gained
    	, olympa_weekly.weekly
      , MAX( olympa.date ) as date
    	FROM olympa
    INNER
    JOIN (
    	SELECT rank
    	, MAX( date ) AS latest_date
    	FROM olympa_gained
    	  GROUP
    		BY rank
    	  ) AS m ON m.rank = olympa.rank
    INNER
    JOIN (
    	SELECT rank
    	, MAX( date ) AS wlatest_date
    	FROM olympa_weekly
    	  GROUP
    		BY rank
    	  ) AS n ON n.rank = olympa.rank
    INNER
    JOIN olympa_voc
    ON olympa_voc.name = olympa.name AND olympa.date = m.latest_date
    INNER
    JOIN olympa_gained
    ON olympa_gained.rank = olympa.rank AND olympa_gained.date = m.latest_date
    INNER
    JOIN olympa_weekly
    ON olympa_weekly.rank = olympa.rank AND olympa_weekly.date = n.wlatest_date
    WHERE olympa.date = m.latest_date
    GROUP BY olympa_gained.rank ORDER BY olympa.experience DESC

    What's wrong here?

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,411
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    INNER
    JOIN olympa_voc
    ON olympa_voc.name = olympa.name AND olympa.date = m.latest_date

    You can eliminate that AND condition here.

  13. #13
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I do but with no effect at all.
    Time execution doesn't changed. ( +- 10 seconds , that's not much | 172 seconds execution)

  14. #14
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you done an EXPLAIN? That should show you what indexes are being used and also, where to add some if needed.

    bazz

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    try not to do any GROUP BY in the outer query

    instead, for the voc table, use a subquery like you did for the gained and weekly tables
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks thats worked.

    Code:
    SELECT olympa.rank
    , olympa.name
      , olympa_voc.voc
      , olympa.level
    	, olympa.experience
    	, olympa_gained.gained
    	, olympa_weekly.weekly
      , MAX( olympa.date ) as date
    	FROM olympa
    INNER
    JOIN (
    	SELECT rank
    	, MAX( date ) AS latest_date
    	FROM olympa_gained
    	  GROUP
    		BY rank
    	  ) AS m ON m.rank = olympa.rank
    INNER
    JOIN (
    	SELECT rank
    	, MAX( date ) AS wlatest_date
    	FROM olympa_weekly
    	  GROUP
    		BY rank
    	  ) AS n ON n.rank = olympa.rank
    INNER
    JOIN (
    	SELECT name
    	FROM olympa_voc
    	  GROUP
    		BY name
        	  ) AS z ON z.name = olympa.name
    INNER
    JOIN olympa_voc
    ON olympa_voc.name = olympa.name
    
    INNER
    JOIN olympa_gained
    ON olympa_gained.rank = olympa.rank 
    AND olympa_gained.date = m.latest_date
    
    INNER
    JOIN olympa_weekly
    ON olympa_weekly.rank = olympa.rank 
    AND olympa_weekly.date = n.wlatest_date
    
    WHERE olympa.date = m.latest_date
    ORDER BY olympa.experience DESC

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    don't forget to remove the MAX from the outer query's SELECT clause, and add it to the z subquery

    and what about that WHERE condition? are you sure you want the olympa row that matches the latest gained date?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes , that's what I've expected.

    I am getting right now something like this one.


    But instead of this , I want to get all the 300 rows from current day.

    GROUP BY?

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    all rows from current day?

    in that case, you want somecolumn = CURRENT_DATE in your WHERE clause, yes?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm I can't specify a date = current date , cause not all tables have a current date.

    Cause of kinda slow speed of update rows in one of my table , there's no current date.

    that's olympa_voc.voc , all others have a current date.

    That's what I 've got if I do a query without olympa_voc.voc

    Code:
    SELECT olympa.rank
    , olympa.name
      , olympa.level
    	, olympa.experience
    	, olympa_gained.gained
    	, olympa_weekly.weekly
      , MAX( olympa.date ) as date
    	FROM olympa
    INNER
    JOIN (
    	SELECT rank
    	, MAX( date ) AS latest_date
    	FROM olympa_gained
    	  GROUP
    		BY rank
    	  ) AS m ON m.rank = olympa.rank
    INNER
    JOIN (
    	SELECT rank
    	, MAX( date ) AS wlatest_date
    	FROM olympa_weekly
    	  GROUP
    		BY rank
    	  ) AS n ON n.rank = olympa.rank
    INNER
    JOIN olympa_gained
    ON olympa_gained.rank = olympa.rank 
    AND olympa_gained.date = m.latest_date
    
    INNER
    JOIN olympa_weekly
    ON olympa_weekly.rank = olympa.rank 
    AND olympa_weekly.date = n.wlatest_date
    
    WHERE olympa.date = m.latest_date
    GROUP BY olympa_gained.rank ORDER BY olympa.experience DESC

  21. #21
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for replies guys , especially for Rudy.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •