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

    Join Date
    Apr 2012
    Posts
    33
    Rep Power
    3

    How to Rank a Group


    Hi
    I have a new problem.
    I have an orders table
    I have lots of branches from different companies creating orders into the orders table.
    What I would like to get out of the orders table is the highest ranking branch for each company.
    So i would select a company. This company has however many branches. Then i would like to rank the branches by the amount of orders they have placed and then rank the branches ASC or DESC.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    33
    Rep Power
    3
    Thanks had a look, will give it a try
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    33
    Rep Power
    3
    Hi
    That page doesn't have what I'm looking for
    Let me rephrase it a bit

    Branch1 = 67 orders (= 67 rows in db)
    Branch2 = 150 orders (= 150 rows in db)
    Branch3 = 115 orders (= 115 rows in db)

    I want a Query that will output "Branch2" (branch with most rows)

    In other words a query that counts num_rows for each branch (column name is branch), then looks at which branch has the highest num_rows and then displays that branch to me.

    So the branch who gave me the most orders will then rank highest in my db.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    33
    Rep Power
    3
    Hi
    I found the solution here
    http://stackoverflow.com/questions/4...-from-a-column

    And the exact query for my problem is this
    Code:
    SELECT branch, count( * ) AS count
    FROM orders
    GROUP BY branch
    ORDER BY count DESC
    Hope this helps others
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by jpmul
    And the exact query for my problem is this
    no, it is not

    this gives the branch with the most orders overall

    in post #1 you said you wanted the branch with the most orders per company
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    33
    Rep Power
    3
    I thought my first question wasn't clear enough so apologies for confusion. That's why i rephrased it to make it easier to understand.

    But if I had to include the company into the query also then this is what it should actually look like and solves my question
    Code:
    SELECT branch, count( * ) AS count
    FROM orders where company =  '$PostedCompany'
    GROUP BY branch
    ORDER BY count DESC

IMN logo majestic logo threadwatch logo seochat tools logo