Acck! Yes, please please do that. O(n) instead of O(n^2) (*). This is a much better solution.
I was trying to convert 'there exists n such that for all m where n != m, n >= m, return n'. Damn logic. (*) To be honest, I'm not even sure what the runtime of my original query was. A good query optimiser would have looked at it, realised I was probably drunk, and just refused to run it. At 05:50 PM 1/24/2002 -0500, Roger Karnouk wrote: >select max(length(firstname)) from TableName; > >-----Original Message----- >From: Michael Stearne [mailto:[EMAIL PROTECTED]] >Sent: Thursday, January 24, 2002 4:38 PM >To: Christopher Thompson >Cc: [EMAIL PROTECTED] >Subject: Re: Tighly packed table > > >Christopher Thompson wrote: > > > At 04:10 PM 1/24/2002 -0500, Michael Stearne wrote: > > > >> We have a somewhat large read-only table (2.9 million recs). I am > >> wonder if there is a utility that will look at each row of each > >> columns and come up with a summary of the largest field (in character > >> length) for each column. For example, scan each row's firstname > >> field and report that the longest first name is 12 characters. That > >> way I can ALTER the firstname field to be a char or varchar of 12? > >> What would be better BTW? > > > > > > I don't know if CHAR or VARCHAR is better for you but as to the query > > here, it would seem easiest to write a short program to query all the > > rows and programatically determine the longest column length. > > > > That said, you could probably set up a SQL statement for it. There's > > a LENGTH function in SQL, isn't there? The statement would look > > SIMILAR to the following: > > > > SELECT MAX(LENGTH(t1.FIRSTNAME)) AS fnamelength FROM TableFoo t1, > > TableFoo t2 WHERE LENGTH(t2.FIRSTNAME) <= fnamelength; > >Looks good to me, thanks. > >Michael > > > > > (Please note that my university SQL instructor pointed out that I > > wrote SQL statements backwards to anyone else he had ever taught. For > > that matter, I did Prolog backwards, too. :) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
