Advanced Search

Results 1 to 2 of 2

Thread: Find the top 3

  1. #1
    Join Date
    Jul 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Find the top 3

    Hi folks, hope you can help.

    Say I had a table like this.

    ID | Name | Location
    ------------------------
    1 | Peter | Scotland
    2 | Ron | Wales
    3 | Alan | Scotland
    4 | John | Scotland
    5 | Michael | Wales
    6 | Ian | England

    What i'm trying to do is get the totals of each location and order them in descending order. Bear with me as I confuse myself here.

    So the output would be something like this:

    Scotland 3
    Wales 2
    England 1

    I know I could run a query and then get the recordcount but is there any SQL function that would allow me to return the data and sort descendingly.

    Thanks for staying with me.

  2. #2
    Join Date
    Jul 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    I managed to get it.

    For anyone interested, the query is as follows:

    SELECT Location, COUNT(*) AS Total
    FROM mytable
    GROUP BY Location
    ORDER BY 2 DESC;

    Cheers

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
  •