SQL Sort

Hi

I’m working with an access database for an estate agents web site. I want to get a list of the 32 most popular locations with a count of the number of properties in each. However, I want to sort the top 32 alphabetically by location name. Is there are clever way of doing this using SQL.

Presently I just have this to return the count of properties in each location:

SELECT count(propertyid) as noprops, location, properties.locationid FROM properties INNER JOIN locations ON locations.locationid=properties.locationid WHERE properties.active=true  GROUP BY location, properties.locationid 

I suppose I can always fall back on pulling off the top 32 records save them into an array and perform a bubble sort on the data, but wondered if SQL might be able to do it for me.

Thanks in advance

how do you define “most popular”?

By the number of properties listed in that location. Hence the

count(propertyid) as noprops

in the original SQL.

All you have to do is order by the count:


SELECT location
  , properties.locationid 
     , COUNT(propertyid) AS noprops
  FROM properties 
 INNER JOIN locations ON locations.locationid = properties.locationid 
 WHERE properties.active = true
 GROUP BY location, properties.locationid
 ORDER BY COUNT(propertyid)

And if you only want 32, then you use TOP 32 (for SQL Server) or [URL=“http://www.google.com/url?sa=t&rct=j&q=&esrc=s&frm=1&source=web&cd=1&ved=0CCkQFjAA&url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.1%2Fen%2Fselect.html&ei=5sjfTvbhJcrX0QGH69GNBw&usg=AFQjCNHGZ–Ab6XK6lL0Oi5anabN_O6N5w”]LIMIT 32 (for mySQL)

just for the sake of clarity, i would reverse the order that the tables are mentioned in the FROM clause

SELECT TOP 32
       locations.id
     , locations.location 
     , COUNT(properties.id) AS noprops
  FROM locations
INNER 
  JOIN properties 
    ON properties.locationid = locations.locationid
   AND properties.active = TRUE
GROUP 
    BY locations.id
     , locations.location 
 ORDER
    BY noprops DESC

also important is the DESC on the ORDER BY

and of course in msaccess it’s TOP

now, how to re-sort these top 32 into location name sequence? by pushing the above query into a subquery

SELECT *
  FROM ( SELECT TOP 32
                locations.id
              , locations.location 
              , COUNT(properties.id) AS noprops
           FROM locations
         INNER 
           JOIN properties 
             ON properties.locationid = locations.locationid
            AND properties.active = TRUE
         GROUP 
             BY locations.id
              , locations.location 
          ORDER
             BY noprops DESC ) AS top32
ORDER
    BY location  

notice two things…

first, this is a perfectly valid use of the otherwise dreaded, evil “select star” – it’s immediately obvious which columns are covered by the asterisk

secondly, all columns in the subquery are properly qualified by their table name, but this is not necessary in the outer query

neat, eh?

:slight_smile:

Thanks r937 once again your clear thoughtful explanations are a real help to understanding, but I am having one or two problems, I guess because its fussy Access SQL that I’m contending with. First up I had to bracket the JOIN statement and now its complaining about no value given for one or more required parameters, and its the ORDER BY ‘noprops’ value it appears to be complaining about:

SELECT *
  FROM ( SELECT TOP 32
                locations.locationid
              , locations.location 
              , COUNT(properties.propertyid) AS noprops
           FROM locations
         INNER 
           JOIN properties 
             ON ((properties.locationid = locations.locationid)
            AND (properties.active = TRUE))
         GROUP 
             BY locations.locationid
              , locations.location 
          ORDER
             BY noprops DESC ) AS top32
ORDER
    BY location

I’ve tried switching the “ORDER BY nopros DESC” to be “ORDER BY Count(properties.propertyid) DESC” and whilst I by-pass the error, it doesn’t sort correctly. Any thoughts?

doesn’t sort correctly?

run the subquery by itself to make sure – don’t forget your outer query re-sorts into location sequence

nice job coming up with the parentheses for the compound join conditions – msaccess is really picky about those, but in regular SQL they aren’t required

Hi r937

I’ve ran the subquery by itself, it still returns the same error: “no value given for one or more required parameters” and then highlights the ‘noprops’ value in the ORDER BY clause.

But if I swap ‘noprops’ for count(properties.propertyid) then it works AND it DOES sort correctly, sorry I was being dense!

So yeah I’ve worked it out and fixed it. The correct code is:

SELECT *
  FROM ( SELECT TOP 32
                locations.locationid
              , locations.location 
              , COUNT(properties.propertyid) AS noprops
           FROM locations
         INNER 
           JOIN properties 
             ON ((properties.locationid = locations.locationid)
            AND (properties.active = TRUE))
         GROUP 
             BY locations.locationid
              , locations.location 
          ORDER
             BY COUNT(properties.propertyid) DESC ) AS top32
ORDER
    BY location 

For some reason MSAccess won’t let you use an alias in the ORDER BY clause. Messy huh?

But thanks again for your help.