[RESOLVED] MSSQL guy has a MySQL statement.... question.

[setaside]

Hey guys. This question is here because it seems that the MySQL gurus would hang out in here AND most of focus in the Database forum is centered around MSSQL.

I've recently picked up PHP/MySQL for a couple of projects that I'm working on. One problem that I seem to be having is that I want to return a top 10 list of sorts into a list box. The statement that I've written works great in a MSSQL database that I use for testing, but when I try to execute the statement on my webserver I get an incorrect syntax error. Any ideas on where my syntax has gone wrong? Here is my MSSQL statement.

SELECT TOP 10 COUNT(*) as Total, Title FROM ggListAvailable GROUP BY Title ORDER BY Total DESC

I'm wondering if it's the TOP keyword or perhaps the Alias. Are these guys supported in MySQL? I looked through the MySQL manual and didn't find anything on the TOP command. If the above statement will not work in MySQL, what suggestions would you have for doing the same thing? Thanks in advance for any help guys.

[The Hobo]

TOP is not a MySQL keyword.

[The Hobo]

If you just want the first 10 records, use the LIMIT keyword, ie:

SELECT Total, Title FROM ggListAvailable GROUP BY Title ORDER BY Total DESC LIMIT 10

[setaside]

Excellent! I had to add the Count(*) back in to your statement, but it works great. thanks much.

[The Hobo]

No problem. What does Count(*) do, by the way?

[setaside]

I did the count(*) so that I could get sum counts of all like named entries. Say there were 23 instances of an item, the query would return 23 as the count for that item. I ordered them by count and then put them in descending order so that the item with the most instances was first. Then I wanted to grab the top ten of those so that a user could see on the fly what the 10 most popular entries were.

Thanks again.

[phpman]

count is almost like num_rows hobo, but for only the query it isits in. you can also do like this

count(field_name) as total, field_name form table where field_name = 'something'

then if you echo out total it will give you the number of rows it found with 'something' in it.