Hi Petr, Ignatius,

What results do you want the query to return?

Both of the queries listed below should return the following results: 3 4 A T You are asking to sort the first character in each row. The numbers should come first and then the upper case letters (as they are all of the same TEXT type). The 'GROUP BY' clause then effectively eliminates the duplicate entries.

If you want to see all of the rows, leave out the 'GROUP BY' clause. Without the 'GROUP BY' clause you should get the following results: 3 3 4 A T

The 'GROUP BY' clause groups all of the like results under one display heading. The group by clause is primarily designed for use with aggregate functions like SUM(), AVG(), COUNT() etc. The group by clause does not lose the duplicate rows but does not display them. You can see this by adding a count to your query: SELECT SUBSTRING(TEXT2,1,1) AS c, COUNT(SUBSTRING(TEXT2,1,1)) AS 'num' FROM t GROUP BY c ORDER BY c;

Does this help or make any sense?

-- Brian Richardson bria...@aol.com http://brians.sytes.net/

-- In a message dated 4/28/2003 12:49:44 AM Central Daylight Time, igna...@free.fr writes: > I replicated your bug with 4.0.12. Probably the engine "forgets" to resolve > the type heterogeneity? > > I found a workaround: > > SELECT CONCAT( SUBSTRING(TEXT2,1,1), '' ) AS c > FROM t > GROUP BY c > ORDER BY c; > > I found that in many instances you have to force the type by doing such a > fudge - ex: ROUND( expr, n ) to force decimal type in a CREATE TEMPORARY > TABLE SELECT ... > > HTH > Ignatius

In a message dated 4/27/2003 10:32:06 PM Central Daylight Time, pe...@practisoft.cz writes: > I find very interesting bug in MySQL 3.23.54 in sorting. > > How to repeat: > CREATE TABLE t TEXT1 VARCHAR(50) NOT NULL, TEXT2 VARCHAR(50) NOT NULL; > ALTER TABLE t ADD INDEX TEXT1 (TEXT1); > ALTER TABLE t ADD INDEX TEXT2 (TEXT2); > INSERT INTO t SET TEXT1='31. some text', TEXT2='32. SOME TEXT'; > INSERT INTO t SET TEXT1='A some text', TEXT2='A SOME TEXT'; > INSERT INTO t SET TEXT1='4. some texts', TEXT2='4. SOME TEXTS'; > INSERT INTO t SET TEXT1='3. some texts', TEXT2='3. SOME TEXTS'; > INSERT INTO t SET TEXT1='The some text', TEXT2='THE SOME TEXT'; > > TEXT2 contain upper case of TEXT1 for every rows. > Now I can get result of 1st character from TEXT2 grouped by this character > and sorteb by this character. > I write query: > SELECT SUBSTRING(TEXT2,1,1) AS c FROM t GROUP BY c ORDER BY c; > and result is very curious > 4 > 3 > A > T > > How to resolve: > I don't know :-) > > Petr Vileta, Czech Republic > http://www.practisoft.cz