Page 1 of 1

MySQL functions question... [Solved]

Posted: Sun Nov 20, 2005 7:00 am
by mjseaden
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

Posted: Sun Nov 20, 2005 7:16 am
by m3mn0n
Something like the following can help you:

Code: Select all

SELECT * FROM `table` WHERE `LocationID` = '$selectedlocation' ORDER BY `Clicks` DESC LIMIT 5
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

Posted: Sun Nov 20, 2005 7:17 am
by mjseaden
Thanks very much

Posted: Sun Nov 20, 2005 7:19 am
by m3mn0n
No problemo :)

Posted: Sun Nov 20, 2005 7:38 am
by mjseaden
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

Posted: Sun Nov 20, 2005 7:47 am
by mjseaden
I think this should work:

SELECT DISTINCT LocationID FROM Properties ORDER BY Clicks LIMIT 5

..
Mark