SQL help plz
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: SQL help plz
At 21:36 +0200 1/30/02, P.Agenbag 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. Create another table to hold the maximum date associated with each key value (which I'm calling k rather than key because key is a reserved word): CREATE TABLE t2 SELECT k, MAX(date) AS date FROM t GROUP BY k; Then join this table with the original to get the rows with the appropriate k and date values, printing out the name and date values: SELECT t.name, t.date FROM t, t2 WHERE t.k = t2.k and t.date = t2.date ORDER BY name; - 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: SQL help plz
mysql describe mytable; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(11) | YES | | NULL| | | keyq | int(11) | YES | | NULL| | | name | char(20) | YES | | NULL| | | dateq | date | YES | | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) select name,max(dateq) from mytable group by name; -Original Message- From: P.Agenbag [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 1:36 PM To: mysql Subject: SQL help plz 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 - 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: SQL help plz
Sorry, I meant: mysql select name,max(dateq) from mytable group by keyq; -Original Message- From: Rick Emery Sent: Wednesday, January 30, 2002 2:14 PM To: 'P.Agenbag'; mysql Subject: RE: SQL help plz mysql describe mytable; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(11) | YES | | NULL| | | keyq | int(11) | YES | | NULL| | | name | char(20) | YES | | NULL| | | dateq | date | YES | | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) select name,max(dateq) from mytable group by name; -Original Message- From: P.Agenbag [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 1:36 PM To: mysql Subject: SQL help plz 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 - 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: SQL help plz
Further, I'd advise NOT using field names like date and key. Using eserved words is never a good prcatice. -Original Message- From: Rick Emery Sent: Wednesday, January 30, 2002 2:16 PM To: 'P.Agenbag'; 'mysql' Subject: RE: SQL help plz Sorry, I meant: mysql select name,max(dateq) from mytable group by keyq; -Original Message- From: Rick Emery Sent: Wednesday, January 30, 2002 2:14 PM To: 'P.Agenbag'; mysql Subject: RE: SQL help plz mysql describe mytable; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(11) | YES | | NULL| | | keyq | int(11) | YES | | NULL| | | name | char(20) | YES | | NULL| | | dateq | date | YES | | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) select name,max(dateq) from mytable group by name; -Original Message- From: P.Agenbag [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 1:36 PM To: mysql Subject: SQL help plz 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 - 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: 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
