Re: SQL help plz
Try this: select key_col, min(name), max(date_col) from my_table group by key_col ; You could use max(name) instead of min(name) also, although since the names can be misspelled, I don't see why it would matter which name is displayed. s.s. On Wed, 30 Jan 2002 21:36:04 +0200, you wrote: Hi I need help with an sql string: have a table similar to this: idkeynamedate 1 123name1 date1 2 123name1 date2 3 111name2 date1 4 111name2 date2 5 123name1 date3 Now, I need sql to report the following result: name1 date3 name2 date2 thus, report all the names in the table but only the ones with the latest date. The sql MUST use the key to compare the different rows with each other and NOT the name, as the name is prone to spelling errors (ok, the key as well, but it's easier to make a typo with letters than with digits and I already have a couple of entries where the names of the entries are different. - 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
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 tables? Or have you tried this on
multiple tables to get the same effect?
Neil:
Yes, I tried it on other tables, and with various data types also.
Perhaps it is a version-specific thing. I'm running 3.23.47. What
version are you running?
Thanks,
s.s.
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 a mysql prompt will
cause the MySQL server to crash and restart.
create temporary table wassup (
rowid int not null AUTO_INCREMENT PRIMARY KEY,
col1 varchar(10) null );
insert into wassup
(col1)
values
('aaa'),
('AAA'),
(NULL)
;
select distinct binary col1 from wassup;
-
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
