Jump to content


Photo

MAX() problem


  • Please log in to reply
11 replies to this topic

#1 Ragnarok

Ragnarok
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 01 March 2006 - 05:23 PM

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:
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`)
);
I want to count all of the ID's with the most recent date. I have tried:
SELECT COUNT(*) as total FROM pages GROUP BY date HAVING MAX(date)
returns 0
SELECT COUNT(MAX(date)) FROM pages GROUP BY date
but that returns this error
#1111 - Invalid use of group function

Does anyone know of a solution?
[url=http://getfirefox.com/][/url]

#2 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 01 March 2006 - 05:43 PM

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

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 March 2006 - 07:44 PM

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

SELECT COUNT(*) as total FROM pages WHERE date = ( SELECT MAX(date) FROM pages )

Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 01 March 2006 - 07:46 PM

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:
SELECT COUNT(*) as total, DATE(date) as justdate FROM pages GROUP BY justdate ORDER BY justdate DESC LIMIT 1
That one will be just as slow as fenway's. This one will run a lot faster though:
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)


#5 Ragnarok

Ragnarok
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 01 March 2006 - 10:40 PM

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?
[url=http://getfirefox.com/][/url]

#6 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 01 March 2006 - 11:19 PM

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

#7 Ragnarok

Ragnarok
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 01 March 2006 - 11:35 PM

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
[url=http://getfirefox.com/][/url]

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 March 2006 - 02:56 AM

That would be much better; use the following:

SELECT COUNT( DISTINCT id ) FROM pages

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 02 March 2006 - 04:01 AM

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.

#10 Ragnarok

Ragnarok
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 02 March 2006 - 01:34 PM

Thank-you very much for your help
[url=http://getfirefox.com/][/url]

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 March 2006 - 03:22 PM

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:

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 |
+----+-------------+--------+--------+---------------+---------+---------+--------+--------+------------------------------+

Just thought you might be interested.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#12 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 02 March 2006 - 08:10 PM

I stand corrected - good to know, thanks.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users