Page 2 of 2 First 12
  • Jump to page:
    #16
  1. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,936
    Rep Power
    379
    I think there's another example at the link I provided which does exactly what you're after. I just assumed you'd want to handle ties this way.
  2. #17
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    14
    Rep Power
    0
    Ah, you're right.

    Though I did'n find anything about including the SET @rank = 0 in the same query. Is it possible?

    Thank you again
  4. #18
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,936
    Rep Power
    379
    I don't think the hypothetical scenario you describe is theoretically possible. If I'm wrong, cross that bridge when you come to it.
  6. #19
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    1
    Rep Power
    0
    newby question:

    I like the very fast solution to ranking, but why do I get nothing but NULL's when executing the most simple query (in mysql query browser) using variables. For example:

    SET @pos=0;
    SELECT @pos:=@pos+1 as row FROM mytable;

    Returns:

    row
    NULL
    NULL
    NULL
    ...

    I will ultimately run this in PHP.
    thanks.
  8. #20
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2002
    Location
    MSP, MN, USA
    Posts
    3
    Rep Power
    0
    Originally Posted by cafelatte
    This from p.697 of The MySQL Cookbook by Paul DuBois:
    http://tinyurl.com/b28cs5
    Code:
    SET @rownum = 0, @rank = 0, @prev_val = NULL;
    SELECT @rownum := @rownum + 1 AS row
         , @rank := IF(@prev_val!=uxp,@rownum,@rank) AS rank
         , @prev_val := uxp AS uxp
      FROM 
         ( 
           YOUR SUBQUERY HERE
         ) t
     ORDER 
        BY uxp DESC;
    Put the query that gives you this resultset between the brackets above.
    Code:
     uID         uXP 
    -------------------
       8         411171 
      25         353944 
     609         248954 
      16         237456 
    6030         229663
    I'm trying the above with
    Code:
    SET @rownum = 0, @rank = 0, @prev_val = NULL;
    SELECT @rownum := @rownum + 1 AS row
         , @rank := IF(@prev_val!=uxp,@rownum,@rank) AS rank
         , @prev_val := uxp AS uxp
      FROM 
         ( 
           select st.store_id as uid, count(st.store_id) AS uxp
            from store_traffic as st
            group by st.store_id
         ) t
     ORDER 
        BY uxp DESC;
    where the subselect resultset is:
    Code:
    uid   uxp
    ----  ---
    4     2
    16    1
    75    1
    283   1
    441   2
    1894  1 
    2638  1
    3284  1
    but when I execute the query, the resultset is null for row and rank; "uxp" has the correct values and the resultset appears to be sorted correctly. Any idea why? I'm assuming it has something to do with the count function I'm using...

    Thanks,
    Matthew
  10. #21
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    10
    Rep Power
    0

    explaination of the query


    Originally Posted by r937
    Code:
    SELECT COUNT(*) AS Rows
         , ( select count(*)+1
               from (
                    select Manufacturer
                         , count(*) as c
                      from Cars
                    group
                        by Manufacturer
                    ) as m
              where c >
                    (
                    select count(*) 
                      from Cars
                     where Manufacturer = T.Manufacturer
                    )
           ) AS RANK
         , Manufacturer 
      FROM Cars AS T
    GROUP 
        BY Manufacturer
    ORDER
        BY RANK

    Hello sir,
    Can you please explain me how this query executes..i mean the flow of this query .
    Thanks
  12. #22
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,383
    Rep Power
    4285
    Originally Posted by radhika25
    Hello sir,
    Can you please explain me how this query executes..i mean the flow of this query .
    Thanks
    please create some test tables with comprehensive data that fits the above scenario, and post them here

    then i will explain how the query works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #23
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    please create some test tables with comprehensive data that fits the above scenario, and post them here

    then i will explain how the query works
    I dont know exactly it is correct or not but below i have mentioned the "cars" table containing various manufacturers :

    id Manufacturer
    1 tata
    2 suzuki
    3 tata
    4 maruti
    5 tata
    6 suzuki
    7 maruti
    8 maruti
    9 maruti

    I am new to these kind of queries so if i did any mistake in creating table so please correct me sir.
  16. #24
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,383
    Rep Power
    4285
    okay, that will do, i guess

    now, please run this and tell me what you get --
    Code:
    select Manufacturer
         , count(*) as c
      from Cars
    group
        by Manufacturer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #25
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    10
    Rep Power
    0
    Now it will have two columns one manufacturer and other one is count. The query returns tata as 3, suzuki as 2 and maruti as 4.
  20. #26
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,383
    Rep Power
    4285
    Originally Posted by radhika25
    The query returns tata as 3, suzuki as 2 and maruti as 4.
    excellent

    now, this next part, you will have to do manually

    i want you to consider suzuki -- how many manufacturers have a count that is greater than suzuki's count?

    what about tata -- how many manufacturers have a count that is greater than tata's count?

    finally, maruti -- how many manufacturers have a count that is greater than maruti's count?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #27
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    excellent

    now, this next part, you will have to do manually

    i want you to consider suzuki -- how many manufacturers have a count that is greater than suzuki's count?

    what about tata -- how many manufacturers have a count that is greater than tata's count?

    finally, maruti -- how many manufacturers have a count that is greater than maruti's count?

    Yes understood your query now but just one doubt - This t.manufacturer represents specific manufacturer ? right?
  24. #28
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,383
    Rep Power
    4285
    Originally Posted by radhika25
    I understood your question but how to do this i dont know....
    just look at the counts and figure it out in your head

    "The query returns tata as 3, suzuki as 2 and maruti as 4."

    how many manufacturers have a count that is greater than suzuki's count?

    how many manufacturers have a count that is greater than tata's count?

    how many manufacturers have a count that is greater than maruti's count?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #29
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    just look at the counts and figure it out in your head

    "The query returns tata as 3, suzuki as 2 and maruti as 4."

    how many manufacturers have a count that is greater than suzuki's count?

    how many manufacturers have a count that is greater than tata's count?

    how many manufacturers have a count that is greater than maruti's count?
    got it sir.
    thanks
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo