Group By Function (Mysql 4.1 versus Mysql 5+)

[TheMayhem]

I come to you guys for a solution on a problem I honestly cannot figure out. I'm a developer for addons for the popular vBulletin script and I have been using the group by function for statistics. The query is listed as:

 

SELECT *, COUNT(marketid) FROM market_transactions

    GROUP BY marketid ORDER BY COUNT( marketid ) DESC LIMIT 0 , 1;

 

It's a nice looking query that works for 90% of the users including myself. My problem is for the last 10% they are getting a php/mysql error stating:

 

MySQL Error  : Invalid use of group function

Error Number  : 1111

 

I looked into it a bit and found the similarities with these users are that all are using mysql version 4.1 instead of mysql 5x series. I can only assume that this is the reason why they are getting this error but don't know how exactly to rescript this mysql/php query to work for them as well or run an alternative for them that accomplishes the same thing. The goal of the query is simply to group everything by the marketid column within the mysql database. Count what has the most table rows for a specific market id and sort it in a descending order to display what is the most popular marketid.

[Mchl]

1. Tell them to upgrade. Seriously, the longer you support outdated platforms, the longer they stay around and need support. Even Sun/Oracle is no longer supporting 4.1, so why should you.

 

2. Even in MySQL 5.x this query is likely to give you bogus results. It is not nice looking. Try

SELECT * FROM market_transactions AS mt CROSS JOIN (
  SELECT marketid, COUNT(*) AS cnt FROM market_transactions GROUP BY marketid ORDER BY cnt DESC LIMIT 1
)  USING (marketid)

 

 

[TheMayhem]

1. Tell them to upgrade. Seriously, the longer you support outdated platforms, the longer they stay around and need support. Even Sun/Oracle is no longer supporting 4.1, so why should you.

 

2. Even in MySQL 5.x this query is likely to give you bogus results. It is not nice looking. Try

SELECT * FROM market_transactions AS mt CROSS JOIN (
  SELECT marketid, COUNT(*) AS cnt FROM market_transactions GROUP BY marketid ORDER BY cnt DESC LIMIT 1
)  USING (marketid)

 

 

Will a query like that work with mysql 4.x series? Unfortunately, the scripts I am working with still support mysql 4x and therefore what I create as addons should follow suite until the script no longer supports it.

[luca200]

I think it will not work with mysql 4.0

Anyway, if you have enough of the marketid value and don't need the other columns of the table, the inner select of his query can work it out for you.

 

[Mchl]

It should work according to this article

http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html

[TheMayhem]

Using the suggested query statement above outputs the following mysql error:

 

MySQL Error  : Every derived table must have its own alias

Error Number  : 1248

 

Any ideas?

[Mchl]

Yeah. Forgot to add alias

 

SELECT * FROM market_transactions AS mt CROSS JOIN (
  SELECT marketid, COUNT(*) AS cnt FROM market_transactions GROUP BY marketid ORDER BY cnt DESC LIMIT 1
) AS sq USING (marketid)

 

See 'AS sq' in there? That's alias definition for this subquery.