Jump to content

Archived

This topic is now archived and is closed to further replies.

Ragnarok

MAX() problem

Recommended Posts

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?

Share this post


Link to post
Share on other sites
This is a MySQL question, not a PHP question - Moved to our MySQL forum.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.