Re: SELECT DISTINCT uses index but is still slow

0.01 seconds is so fast that I wonder if that's actually because the query cache is storing the query. Do you have query cache enabled? James At 6:35 am + 5/1/06, C.R.Vegelin wrote: >Hi James, >I have found similar - slowdown - effects for queries. >However, it is not always clear what causes

Re: SELECT DISTINCT uses index but is still slow

Hi James, I have found similar - slowdown - effects for queries. However, it is not always clear what causes the lack of speed. For example, I have a table with more than 9 million rows, including a non-unique indexed item myKey (tinyint). The query "Select myKey, count(*) from myTable Group By my

Re: SELECT DISTINCT

Scott Noyes wrote: Here's one (not very clean, but it works) way to do it: SELECT id, params FROM table GROUP BY params; The trouble is, how do you know which id should come with it? If you table is id param 1 1 2 1 should the query return 1, 1 or 2, 1 ? This is not really what I wa

Re: SELECT DISTINCT

[EMAIL PROTECTED] wrote: Depending on what version server you are running, the GROUP_CONCAT() function may be an option for you: SELECT params, GROUP_CONCAT(ID) FROM table GROUP BY params; I'm using 4.1.10a-standard-log Server version, and this is just what I wanted. Thanks. -- Nuno Perei

Re: SELECT DISTINCT

Here's one (not very clean, but it works) way to do it: SELECT id, params FROM table GROUP BY params; The trouble is, how do you know which id should come with it? If you table is id param 1 1 2 1 should the query return 1, 1 or 2, 1 ? -- MySQL General Mailing List For list archives: ht

Re: SELECT DISTINCT

Nuno Pereira <[EMAIL PROTECTED]> wrote on 08/05/2005 02:04:35 PM: > Hello list, > > I'm getting the distinct 'params' columns from a table with this query > > SELECT DISTINCT params FROM table; > > but this gets the rows with distinct 'params' cols, but I want to know > from which row each par

Re: SELECT DISTINCT ROW(A,B) ?

On Sat, 7 May 2005, Roger Baklund wrote: >Dan Bolser wrote: >> SELECT DISTINCT ROW(A,B) from a; >> >> While I am on a roll (of sorts) can I ask if the above syntax should be >> valid or not? > >If you mean the exact syntax above, I think not... it looks like ROW() >is a function taking two para

Re: SELECT DISTINCT ROW(A,B) ?

Dan Bolser wrote: SELECT DISTINCT ROW(A,B) from a; While I am on a roll (of sorts) can I ask if the above syntax should be valid or not? If you mean the exact syntax above, I think not... it looks like ROW() is a function taking two parameters in this case... what does the function return? Is it

RE: SELECT DISTINCT Problem

People, including me, often expect the wrong thing from SELECT DISTINCT, May suggest you do the following: Create table temp_tb_spots as Select * from tb_spots where aired_station = '??' ; select distinct Date from temp_tb_spots group ; In a different window run Select Date from temp_tb_spot

Re: SELECT DISTINCT Problem

I'm not aware of any problems with DISTINCT, though it often seems that how people expect it to work differs from how it actually works. It would help if you could provide more details. For example, what do you mean by "large gaps"? Do you mean there are rows which match the WHERE condition w

RE: SELECT DISTINCT : I've found one trick !

Answer to my query "select distinct" http://lists.mysql.com/mysql/175839 To select properly only one time one element of a column in a list (here the journal name) I have to add a selection on the end of the name that it is not a space or a return ! Both a

Re: SELECT DISTINCT + ORDER BY confusion

If what you mean by "most recent" are the products with the latest 'post_date', try this: SELECT ID, title, max(s.post_date) as post_date FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 2328

RE: SELECT DISTINCT + ORDER BY confusion

If you are usign 4.1 you could try: SELECT DISTINCT d, title FROM (select p.id, p.title from product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc ) limit 10 otherwise

Re: SELECT DISTINCT + ORDER BY confusion

Victor Pendleton wrote: Have you tried using a group by clause? Group by title same problem - the group by happens before the order by and you get essentially random results. -Original Message- From: news To: [EMAIL PROTECTED] Sent: 7/9/04 3:08 PM Subject: SELECT DISTINCT + ORDER BY conf

RE: SELECT DISTINCT + ORDER BY confusion

Have you tried using a group by clause? Group by title -Original Message- From: news To: [EMAIL PROTECTED] Sent: 7/9/04 3:08 PM Subject: SELECT DISTINCT + ORDER BY confusion I've got a product & story setup where there can be multiple stories of a given type for any product. I want to f

Re: Select distinct year from unix timestamp

From: "T. H. Grejc" > I'm creating news archive and it should be sorted by months: > > January 2004 (news count is 56) > February 2004 (48) > ... So you need to use GROUP BY and COUNT. The format is like this: select monthandyear,count(othercolumn) from t group by monthandyear in your case mon

Re: [SPAM]Re: Select distinct year from unix timestamp

At 0:25 +0200 5/17/04, John Fawcett wrote: From: "Paul DuBois"> At 22:27 +0200 5/16/04, John Fawcett wrote: >Year does not operate on a unix timestamp. Sure it does: mysql> select t, year(t) from tsdemo1; ++-+ | t | year(t) | ++-+

Re: Select distinct year from unix timestamp

John Fawcett wrote: From: "T. H. Grejc" How can I add more fields to query. If I write: SELECT DISTINCT FROM_UNIXTIME(created, '%Y %M'), other_field FROM table_name ORDER BY created DESC I loose distinction (all dates are displayed). TNX I don't think distinction is lost. All the rows should still

Re: [SPAM]Re: Select distinct year from unix timestamp

From: "Paul DuBois"> At 22:27 +0200 5/16/04, John Fawcett wrote: > >Year does not operate on a unix timestamp. > > Sure it does: > > mysql> select t, year(t) from tsdemo1; > ++-+ > | t | year(t) | > ++-+ > | 20010822133241 |2001 | >

Re: Select distinct year from unix timestamp

From: "T. H. Grejc" > How can I add more fields to query. If I write: > > SELECT DISTINCT FROM_UNIXTIME(created, '%Y %M'), other_field FROM > table_name ORDER BY created DESC > > I loose distinction (all dates are displayed). > > TNX > I don't think distinction is lost. All the rows should still be

Re: Select distinct year from unix timestamp

Paul DuBois wrote: At 22:27 +0200 5/16/04, John Fawcett wrote: From: "T. H. Grejc" Hello, I'm trying to select all distinct years from a unixtimestamp field in MySQL database (3.23.56). I have a query: SELECT DISTINCT YEAR(date_field) As theYear FROM table but PHP gives me an empty array. What

Re: Select distinct year from unix timestamp

John Fawcett wrote: From: "T. H. Grejc" Hello, I'm trying to select all distinct years from a unixtimestamp field in MySQL database (3.23.56). I have a query: SELECT DISTINCT YEAR(date_field) As theYear FROM table but PHP gives me an empty array. What am I doing wrong? TNX I think you need this f

Re: Select distinct year from unix timestamp

At 22:27 +0200 5/16/04, John Fawcett wrote: From: "T. H. Grejc" Hello, I'm trying to select all distinct years from a unixtimestamp field in MySQL database (3.23.56). I have a query: SELECT DISTINCT YEAR(date_field) As theYear FROM table but PHP gives me an empty array. What am I doing wrong?

Re: Select distinct year from unix timestamp

From: "T. H. Grejc" > Hello, > > I'm trying to select all distinct years from a unixtimestamp field in > MySQL database (3.23.56). I have a query: > > SELECT DISTINCT YEAR(date_field) As theYear FROM table > > but PHP gives me an empty array. What am I doing wrong? > > TNX > I think you need

RE: Select distinct year from unix timestamp

R u sure your printing out the correct array (hash) field? Did you connect to the db? Is mysql_error reporting an error? DVP Dathan Vance Pattishall http://www.friendster.com > -Original Message- > From: news [mailto:[EMAIL PROTECTED] On Behalf Of T. H. Grejc > Sent: Sunday, Ma

Re: select Distinct question

Hello List, Please forgive this rather lengthy post. I thought I had something worked out to answer Rob's question but when I put it to the test I found what MAY be a bug in 4.1.1a-alpha-nt-log. Here is what I did. I created two tables, tablea and tableb. Here are their defs: mysql> show create

Re: SELECT DISTINCT returns an incorrect result with special char acters

At 23:28 +0200 4/28/04, Jochem van Dieten wrote: Ricardo wrote: Which collation are you using in MS SQL Server? And in MySQL? MS SQL Server 8 Collation = Latin1_General_CI_AS MySQL 4.0.16 character_set = latin1 The charset determines which characters can occur. The collation determines the sort

Re: SELECT DISTINCT returns an incorrect result with special characters

I've solved the problem starting the server using the option --character-set=dos. The side effect is that searches become accent-sensitive. Best wishes. --- Ricardo <[EMAIL PROTECTED]> escreveu: > I have a problem with SELECT DISTINCT if the target > field contains special characters. > > Exa

Re: SELECT DISTINCT returns an incorrect result with special characters

But I think the collation set only applies to MySQL 4.1, doesn't it? I'm using MySQL 4.0.16. I found no "character_set_server" system variable. Only "character_set". http://dev.mysql.com/doc/mysql/en/Charset-map.html Thanks. --- Jochem van Dieten <[EMAIL PROTECTED]> escreveu: > Ricardo wrote:

Re: SELECT DISTINCT returns an incorrect result with special char acters

Ricardo wrote: Which collation are you using in MS SQL Server? And in MySQL? MS SQL Server 8 Collation = Latin1_General_CI_AS MySQL 4.0.16 character_set = latin1 The charset determines which characters can occur. The collation determines the sort order. What is the collation in MySQL? http://dev

Re: SELECT DISTINCT returns an incorrect result with special char acters

> Which collation are you using in MS SQL Server? And > in MySQL? > > Jochem MS SQL Server 8 Collation = Latin1_General_CI_AS MySQL 4.0.16 character_set = latin1 __ Yahoo! Messenger - Fale com seus amigos online. Instale agora

Re: SELECT DISTINCT returns an incorrect result with special char acters

Ricardo wrote: The order should be: fa Fa fá Fá fâ Fâ fã Fã As I get in MS-SQL Server. Which collation are you using in MS SQL Server? And in MySQL? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.

RE: SELECT DISTINCT returns an incorrect result with special char acters

The order should be: fa Fa fá Fá fâ Fâ fã Fã As I get in MS-SQL Server. > --- Victor Pendleton <[EMAIL PROTECTED]> escreveu: > It does not appear to respect the ascii values. > Should the order be? > fa > fá > fâ > fã > fa > fá > fâ > fã

RE: SELECT DISTINCT returns an incorrect result with special char acters

It does not appear to respect the ascii values. Should the order be? fa fá fâ fã fa fá fâ fã -Original Message- From: Ricardo To: [EMAIL PROTECTED] Sent: 4/28/04 10:05 AM Subject: RE: SELECT DISTINCT returns an incorrect result with special char acters - The sort order gets incorrect

Re: SELECT DISTINCT returns an incorrect result with special characters

latin1 >--- Egor Egorov <[EMAIL PROTECTED]> escreveu: > What is the character set of the data? What is the > character set of MySQL server? > > > > -- > For technical support contracts, goto > https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net > http://www.ensita.net/

RE: SELECT DISTINCT returns an incorrect result with special char acters

- The sort order gets incorrect: SELECT DISTINCT BINARY(MyField) FROM MyTable WHERE MyField LIKE 'f%' ORDER BY MyField +-+ | BINARY(MyField) | +-+ | fa | | Fâ | | fá | | Fa | | fã | | Fá

RE: SELECT DISTINCT returns an incorrect result with special char acters

Why can you not write SELECT DISTINCT BINARY(col) FROM table1 WHERE BINARY(col) LIKE 'criteria' ORDER BY col ? -Original Message- From: Ricardo To: Victor Pendleton; [EMAIL PROTECTED] Sent: 4/28/04 9:33 AM Subject: RE: SELECT DISTINCT returns an incorrect result with special c

Re: SELECT DISTINCT returns an incorrect result with special characters

Ricardo <[EMAIL PROTECTED]> wrote: > I have a problem with SELECT DISTINCT if the target > field contains special characters. > > Example: > > select MyField from MyTable > +--+ > | MyField | > +--+ > | f? | > | F? | > | fa | > | Fa | > | f? | >

RE: SELECT DISTINCT returns an incorrect result with special char acters

Thanks Mr. Pendleton. But I cannot change the behaviour of the LIKE operator and the ORDER BY clause, which are going to be affected by both of your suggestions. --- Victor Pendleton <[EMAIL PROTECTED]> escreveu: > Try > SELECT DISTINCT BINARY(col) > ... > Or declare the column as binary ___

RE: SELECT DISTINCT returns an incorrect result with special char acters

Try SELECT DISTINCT BINARY(col) ... Or declare the column as binary -Original Message- From: Ricardo To: [EMAIL PROTECTED] Sent: 4/28/04 9:02 AM Subject: SELECT DISTINCT returns an incorrect result with special characters I have a problem with SELECT DISTINCT if the target field contain

Re: SELECT DISTINCT.. ORDER BY.. DESC - bug??

After a bit of digging, found out that this is indeed a bug: http://bugs.mysql.com/bug.php?id=1274 Strangely enough, it is listed as "CLOSED". Now - this is hard to explain, but it looks like MySQL developers have no answer or interest in dealing with this problem. The fact is - adding an inde

Re: select distinct from two columns

Hi, try to use "group by" clause in your select, e.g. select name, city from mytable group by name, city order by name, city; Best regards, Mikhail. - Original Message - From: "Veysel Harun Sahin" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, January 02, 2004 4:42 PM Subje

RE: select distinct from two columns

select distinct(col1,col2) should work.. Group by most certainly will... Select col1,col2 from table group by col1, col2.. Same thing > Hello, > > The two columns of my table are name and city. I am trying to > do a list which contains only the different names. This can > be done this by disti

RE: select distinct from two columns - solved

:) I have solved the problem. Thanks. [EMAIL PROTECTED] (Veysel Harun Sahin) wrote: >Hello, > >The two columns of my table are name and city. I am trying to do a list which >contains only the different names. This can be done this by distinct keyword. But all >the same names which have differe

Re: select distinct from two columns

* Veysel Harun Sahin > The two columns of my table are name and city. I am trying to do > a list which contains only the different names. This can be done > this by distinct keyword. But all the same names which have > different cities also have to be included in my list. So I can > not do this wit

RE: SELECT DISTINCT question (Thanks it worked)

Thank you!!! It works. Hector -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 13, 2003 1:44 PM To: Personal Cc: [EMAIL PROTECTED] Subject: Re: SELECT DISTINCT question SELECT

Re: SELECT DISTINCT question

SELECT DISTINCT(LEFT (product_number,5)) FROM products. http://www.mysql.com/doc/en/String_functions.html hth Jeff Personal

RE: Select distinct speed on an indexed column

Yeah, I have a similar box like yours. I copied the first column to a new table with an index. I ran select distinct and the query took 6 seconds to execute. This must have to do with the record length, because when I indexed the origional table's first column the query was 1 minute 30 seconds to

RE: Select distinct speed on an indexed column

no Cc: '[EMAIL PROTECTED]' Subject: Re: Select distinct speed on an indexed column Since your primary key is used, maybe you should consider adding an index on PostedZpdi field only. You may also check 'key_buffer_size' value (show variables like 'key%'). Since your

RE: Select distinct speed on an indexed column

> > From: Haydies [mailto:[EMAIL PROTECTED] > Sent: Monday, September 15, 2003 11:19 AM > To: [EMAIL PROTECTED] > Subject: Re: Select distinct speed on an indexed column > > > Its a compound key, they are always slow. I would imagin you will need to > seriously rede

Re: Select distinct speed on an indexed column

Since your primary key is used, maybe you should consider adding an index on PostedZpdi field only. You may also check 'key_buffer_size' value (show variables like 'key%'). Since your primary key is more than 200 MB big, allocating a "big" key buffer (> 256MB) may help. Joseph Bueno Nathan Cassano

RE: Select distinct speed on an indexed column

mysql> explain select distinct AccountLevelId from PostedZpdi; ++---+---+-+-+--+-+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++---+---+-+--

Re: Select distinct speed on an indexed column

Its a compound key, they are always slow. I would imagin you will need to seriously redesign your database to speed that up. I'm not 100% sure how the index is stored, but it would be some what pointless if it was individual field values. Its like haveing "field1field2feild3field4field5" so that a

Re: Select distinct speed on an indexed column

Nathan Cassano wrote: Hey everyone, I have a question about the speed of selecting distinct values on an indexed column. I have a table with a five column primary key and 3,215,540 records. I want to select all of the distinct values of the first column in the primary key. This column only

Re: "select distinct" doesn't coalesce NULL rows

Smurf wrote: > > >Description: > "select distinct FOO from BAR" reports multiple NULL rows > >How-To-Repeat: > Unknown. It's a large table (1 entries or so). The problem > didn't show with a simple test table. > > The table: > > -- MySQL dump 10.0 > -- > -- Ho

Re: SELECT DISTINCT question

PROTECTED]>; "'Juan Nin'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, March 01, 2003 3:46 AM Subject: RE: SELECT DISTINCT question *DISTINCT omits data bases on selected fields only. i.e. if you have thousand rows having different dates in 'my

RE: SELECT DISTINCT question

duplicate fields. regds, -Original Message- From: Sheryl Canter [mailto:[EMAIL PROTECTED] Sent: Saturday, March 01, 2003 01:56 To: Juan Nin; [EMAIL PROTECTED] Subject: Re: SELECT DISTINCT question So depending on which columns you were looking at, two rows might seem like duplicates or no

Re: SELECT DISTINCT question

this correctly? - Sheryl mysql - Original Message - From: "Juan Nin" <[EMAIL PROTECTED]> To: "Sheryl Canter" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, February 28, 2003 2:42 PM Subject: Re: SELECT DISTINCT question From: "Sheryl Ca

Re: SELECT DISTINCT question

x, y, z Form Table is equivalent with SELECT x, y, z FROM Table GROUP BY x, y, z HTH, Tore. - Original Message - From: "Sheryl Canter" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "Frank Peavy" <[EMAIL PROTECTED]> Sent: Friday, February 28, 2003

Re: Re: SELECT DISTINCT question

> From: "Sheryl Canter" <[EMAIL PROTECTED]> > > > > DISTINCT removes duplicates: rows that are the same in *all* columns. > > > > Would this be all columns in the table, or all columns retrieved by the > > SELECT statement? in the SELECT statement :) SELECT queries do not alter your rows, you

Re: SELECT DISTINCT question

L PROTECTED]> To: "Sheryl Canter" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Frank Peavy" <[EMAIL PROTECTED]> Sent: Friday, February 28, 2003 12:34 PM Subject: Re: SELECT DISTINCT question At 11:50 -0500 2/28/03, Sheryl Canter wrote: >Frank, > >Sorry tha

Re: SELECT DISTINCT question

up so I can identify which author name to display. Thanks for your help. - Sheryl - Original Message - From: "Frank Peavy" <[EMAIL PROTECTED]> To: "Sheryl Canter" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, February 28, 2003 11:22 AM S

Re: SELECT DISTINCT question

Sheryl, I'm trying to determine which author has the highest royalty percent FOR EACH PROGRAM, not overall. I'm displaying a list of programs and authors, and when there is more than one author, I want to show the principal author (i.e., the one earning the highest royalty percent). Ok, so your des

Re: SELECT DISTINCT question

nother piece of my site. I'm under > a big deadline crunch. > > I'll be interested to see if others know of other ways of handling this. > > - Sheryl > > > - Original Message - > From: "Tore Bostrup" <[EMAIL PROTECTED]> > To: &q

Re: SELECT DISTINCT question

others know of other ways of handling this. - Sheryl - Original Message - From: "Tore Bostrup" <[EMAIL PROTECTED]> To: "Sheryl Canter" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, February 28, 2003 1:41 AM Subject: Re: SELECT DISTINCT quest

Re: SELECT DISTINCT question

DISTINCT works on the result set and across the entire select list. It will suppress duplicate result *rows*. This is an interesting problem when using MySQL due to the lack of support for nested SELECTs. In other database systems, I'd use a correlated subquery in the where clause, but with MySQ

Re: SELECT DISTINCT question

Frank, > Before you go any further, I would attempt to answer one question. > Are you trying to determine which Author had the highest royalties or are > you trying to determine which Author has the highest royalties in each > program(I am assuming an author can work on multiple programs)? This wi

Re: SELECT DISTINCT question

Sheryl, See my comments below... (1) What is the rule that SELECT DISTINCT uses when deciding which of multiple instances to return? Does it return the first one? How it selects the distinct items is outside my area of knowledge. I would not assume it is the first one or the last one, or anywhere

Re: SELECT DISTINCT question

On 27-Feb-2003 Sheryl Canter wrote: > > SELECT * > FROM programs p, authors a, royalties r > WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID > > I could change this to SELECT DISTINCT * ..., but then which author would I > get? If it's always the first encountered row, then could

Re: SELECT DISTINCT question

: Thursday, February 27, 2003 11:10 AM Subject: Re: SELECT DISTINCT question Sheryl, Sorry I don't time to fully address your question but I will try to give you some feedback. The DISTINCT functionality will give you exact that, every distinct instance of the columns you SELECTED. SELECT DISTINCT

Re: SELECT DISTINCT question

Sheryl, Sorry I don't time to fully address your question but I will try to give you some feedback. The DISTINCT functionality will give you exact that, every distinct instance of the columns you SELECTED. SELECT DISTINCT a.Author FROM author a Will give you each and every author but only once.

Re: SELECT DISTINCT question

Didn't receive answer to message below. Could someone please take a look? TIA. - Sheryl - Original Message - From: "Sheryl Canter" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, February 26, 2003 11:49 AM Subject: SELECT DISTINCT question I need help with a query. I

Re: SELECT DISTINCT *

Maybe if we knew what . . . . . . . . . . means, we could answer. [EMAIL PROTECTED] wrote: Hey all; I am trying to work on a query that has SELECT DISTINCT * .. This does not work. While there is no error, the where clause causes repeats. I do not really expect this to work, as DIST

Re: SELECT DISTINCT *

At 7:54 -0800 1/9/03, [EMAIL PROTECTED] wrote: Hey all; I am trying to work on a query that has SELECT DISTINCT * .. This does not work. While there is no error, the where clause causes repeats. I do not really expect this to work, as DISTINCT needs a column to work with. No, it doesn'

re: SELECT DISTINCT bug MySQL 4.0.3 MAX NT

Glenn, Friday, September 20, 2002, 5:27:01 AM, you wrote: GW> I was able to crash MySQL every time with the following -- it is GW> incorrectly formatted I know and I should be using GROUP BY in this GW> particular case GW> When I do the following query: GW> SELECT DISTINCT * FROM CUSTOMER INNE

Re: SELECT DISTINCT w/LEFT JOIN segfault in 4.0.3

User, Tuesday, September 03, 2002, 7:54:05 AM, you wrote: >Description: UT> A specific query in the format of: UT> SELECT DISTINCT a.*, b.* FROM privatemessage LEFT JOIN b ON (b.x = a.y); UT> Is causing a segfault. This worked fine in the 3.23 series. Thanks for bug report! It's already fixed

Re: SELECT DISTINCT BINARY crashes mysql on null values

[EMAIL PROTECTED] writes: > >Description: > >Executing a SELECT DISTINCT statement in conjunction >with the BINARY cast operator will crash MySQL server >when the column being cast contains one or more null >values. > > > > >How-To-Repeat: > > Executing the following code from

Re: SELECT DISTINCT BINARY crashes mysql on null values

On Wed, 30 Jan 2002 08:21:49 +1000, you wrote: >I tried this on an existing table using a char(50) column with 956 entries, >of which I have 1 valid entry and 954 NULL values. I have 2 records returned >1 = valid entry, 1 = NULL and mysql did not crash. >Is it only when you are using temporary ta

RE: SELECT DISTINCT BINARY crashes mysql on null values

I tried this on an existing table using a char(50) column with 956 entries, of which I have 1 valid entry and 954 NULL values. I have 2 records returned 1 = valid entry, 1 = NULL and mysql did not crash. Is it only when you are using temporary tables? Or have you tried this on multiple tables to g

RE: SELECT DISTINCT - bug?

* Anders Bjarby > Jag har problem med en enkel SELECT DISTINCT, på ett enda fält i en enda > tabell. Har jag missat något mycket grundläggande eller är det en BUG? I would suggest you use english on this list... :) > | indate| timestamp(8) | YES | | NULL|| I t

RE: SELECT DISTINCT

There are no duplicates in your results Distinct is on the whole record not on the individual Items for example: | Xerox N-2125 | Xerox DC255 | is not the same as | Xerox N40| Xerox DC255 | because the first field is different and | Xerox N-2125 | Xerox DC255 | is not the same as | Xerox

RE: SELECT DISTINCT on two columns

Only way I can think of is to use a temp table: CREATE TEMPORARY TABLE test (fruits varchar(100) not null) TYPE=HEAP; INSERT INTO test SELECT DISTINCT fruits1 FROM table; INSERT INTO test SELECT DISTINCT fruits2 FROM table; SELECT DISTINCT fruits FROM test; DROP TABLE test; However this can be s

Re: SELECT DISTINCT or GROUP BY problem

On Monday 07 May 2001 03:37, Andrew Leshkin wrote: > Hello, > > I have the following perfomance problem with simple query on one of my > servers: > > Here is my table ~6 records. > > +-+-+--+-++- > ---+ > | Field | Type

Re: select DISTINCT() hang - but non corrupt table

No, it is not disk space.. it is not table corruption either.. it is server confusion, with no diagnostic information output. Symptom again: hang on straight forward queries involving a total index scan, with either count(*) or distinct(*), usually stuck with 100% cpu use of the thread in 'sendi

Re: select DISTINCT() hang - but non corrupt table

Justin wrote: > > I have a puzzle.. > > Frequently, about, weekly? our data server gets into a state > where > select distinct(keyfield) from table > hangs in the 'sending data' phase, or the 'sorting result' phase, > the client is left waiting for the result, no rows are returned, > no errors

Re: SELECT DISTINCT question

>Say I have a bunch of records in a table with a bunch of fields. One of >those fields is cluster_id. It is not unique. However, I want to make a >unique set. I want one record for each cluster_id value. I don't care >which record that is. > >Right now, I do: >SELECT DISTINCT cluster_id FROM my_ta

Re: SELECT DISTINCT question

> Say I have a bunch of records in a table with a bunch of fields. One of > those fields is cluster_id. It is not unique. However, I want to make a > unique set. I want one record for each cluster_id value. I don't care > which record that is. > > Right now, I do: > SELECT DISTINCT cluster_id FRO