MySQL functions question... [Solved]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

MySQL functions question... [Solved]

Post 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
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post 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
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

Thanks very much
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

No problemo :)
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post 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
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

I think this should work:

SELECT DISTINCT LocationID FROM Properties ORDER BY Clicks LIMIT 5

..
Mark
Post Reply