Hi,
I have a table called 'Properties'. The 'Clicks' column records how many hits on my site the property has acquired. The 'LocationID' column records the locationID of each property.
I want an SQL query that will return the top 5 locations by total clicks for properties in that location (locationID). Can anyone suggest a query that will do this, without having to use intermediate PHP code?
Many thanks
Mark
MySQL functions question... [Solved]
Moderator: General Moderators
Something like the following can help you:
Descending because the top will be the highest number and limiting to 5 as you needed.
Check out the MySQL manual for more information about this sort of thing. It also has good examples.
http://dev.mysql.com/doc/refman/5.0/en/select.html
Code: Select all
SELECT * FROM `table` WHERE `LocationID` = '$selectedlocation' ORDER BY `Clicks` DESC LIMIT 5Check out the MySQL manual for more information about this sort of thing. It also has good examples.
http://dev.mysql.com/doc/refman/5.0/en/select.html
Hi Sami,
The SQL query you stated only finds the top 5 properties in terms of clicks. I'm looking for the top 5 locations in terms of clicks - and this is what it seems very difficult to do without some sort of MySQL function?
essentially something like
SELECT LocationID FROM Properties ORDER BY TOTAL(Clicks, LocationID)
I'm sure there must be a function like this!
Mark
The SQL query you stated only finds the top 5 properties in terms of clicks. I'm looking for the top 5 locations in terms of clicks - and this is what it seems very difficult to do without some sort of MySQL function?
essentially something like
SELECT LocationID FROM Properties ORDER BY TOTAL(Clicks, LocationID)
I'm sure there must be a function like this!
Mark