MAX() problem

[Ragnarok]

I am doing a versioning system so that every time you edit a page, the old page is still kept but the new page is shown insted. I have that working find but when I am trying to list the pages I am having problems getting a query to do it.

I have a table like this:
[code]CREATE TABLE `pages` (
  `id` varchar(25) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `title` varchar(25) NOT NULL default '',
  `content` mediumtext NOT NULL,
  PRIMARY KEY  (`id`,`date`),
  FULLTEXT KEY `Search` (`title`,`content`)
);[/code]
I want to count all of the ID's with the most recent date. I have tried:
[code]SELECT COUNT(*) as total FROM pages GROUP BY date HAVING MAX(date)[/code]
returns 0
[code]SELECT COUNT(MAX(date)) FROM pages GROUP BY date[/code]
but that returns this error
[code]#1111 - Invalid use of group function[/code]

Does anyone know of a solution?

[zq29]

This is a MySQL question, not a PHP question - Moved to our MySQL forum.

[fenway]

The easiest way to do this is with a subquery (4.1+):

[code]SELECT COUNT(*) as total FROM pages WHERE date = ( SELECT MAX(date) FROM pages )[/code]

Hope that helps.

[wickning1]

Not possible to use MAX() the way you want without a subquery. Fenway's is easy but causes a potentially very slow table scan. Also, I'm assuming you would rather group by day, not by second? Grouping the pages updated in the same second doesn't seem useful.

Here's two solutions for grouping on the most recent day:
[code]SELECT COUNT(*) as total, DATE(date) as justdate FROM pages GROUP BY justdate ORDER BY justdate DESC LIMIT 1[/code]
That one will be just as slow as fenway's. This one will run a lot faster though:
[code]SELECT COUNT(*) as total FROM pages a
INNER JOIN (SELECT DATE(MAX(date)) as justdate FROM pages) b
   ON a.date BETWEEN CAST(b.justdate as DATETIME)
   AND CAST(b.justdate + INTERVAL 1 DAY as DATETIME)[/code]

[Ragnarok]

I have been thinking, all I really need to do is count the different ids, it doesn't matter which date goes with it. Is it posible to do it that way? Also would this way be faster?

[wickning1]

You mean "SELECT COUNT(*) as total FROM pages"? Yes that will be very fast.

[Ragnarok]

I meen like if I have a table like this

id|date
page1|2006-01-13
page2|2006-01-26
page1|2006-01-26
page3|2006-02-05
page2|2006-02-09

it would find that there are 3 unique id (page1, page2, page3) names and only count those id names

[fenway]

That would be much better; use the following:

[code]SELECT COUNT( DISTINCT id ) FROM pages[/code]

Which is very well optimized by MySQL.

BTW, regarding wickning1's comment, I simply assumed that this column was indexed. If not, well, yeah, table scan is guaranteed; same goes doing such a sort on any part of date (e.g. using the DATE() function). In that case, you have do use a join to get around this problem, as wickning1 suggested.

[wickning1]

Nope, even with an index, I'm 90% certain that that subquery you wrote will table scan. Ideally it shouldn't, but I know from experience, in 5.0, it still does. The parsing engine can't be absolutely sure that the subquery will be the same for every row, so it assumes that it won't be.

In general, avoid putting subqueries in the WHERE clause. Put them in the FROM clause instead, where the statement will only be evaluated once.

[Ragnarok]

Thank-you very much for your help

[fenway]

I disagree; first, only correlated sub-queries are run every time -- the optimizer takes care of non-correlated ones automatically. I tried it with an index of the date column in 4.1:

[code]EXPLAIN SELECT COUNT(*) as total FROM pages WHERE theDate = ( SELECT MAX(theDate) FROM pages )

+----+-------------+--------+--------+---------------+---------+---------+--------+--------+------------------------------+
| id | select_type | table  | type   | possible_keys | key     | key_len | ref    | rows   | Extra                        |
+----+-------------+--------+--------+---------------+---------+---------+--------+--------+------------------------------+
|  1 | PRIMARY     | pages  | ref    | theDate       | theDate |       3 | const  |      1 | Using where; Using index     |
|  2 | SUBQUERY    | [NULL] | [NULL] | [NULL]        | [NULL]  |  [NULL] | [NULL] | [NULL] | Select tables optimized away |
+----+-------------+--------+--------+---------------+---------+---------+--------+--------+------------------------------+[/code]

Just thought you might be interested.

[wickning1]

I stand corrected - good to know, thanks.