You are right above the max date query and I really appreciate your help, but the subselect query its other question. I try the subselect query with the 4.0.12-max win32 with the --new option in the command line to start the server, according to the manual this should enable the subselect feature, but it doesn’t, so I try with the 4.1 alpha win32 version with the same error:
RROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near............ and so on.... any ideas why I cant use the subselect feature? I missing something about the conf?? maybe my.ini?? -----Original Message----- From: Michael Satterwhite [mailto:[EMAIL PROTECTED] Sent: Lunes, 21 de Julio de 2003 10:54 p.m. To: [EMAIL PROTECTED] Subject: Re: Subselect in 4.0.12-max with --new option / Max Date -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Monday 21 July 2003 18:48, [EMAIL PROTECTED] wrote: > Cod Product date > 10 Nissan Car 2003-06-20 > 20 Guitar Yamaha 2003-07-01 > 10 Nissan Car 2003-05-01 > 30 Stereo Cables 2003-01-20 > 20 Guitar Yamaha 2003-10-05 > 10 Nissan Car 2003-08-10 > 10 Nissan Car 2003-07-20 > 20 Guitar Yamaha 2003-09-10 > > > So I have 3 groups, grouping by Cod 10,20,30 i need to build a query to > get the max date of every group, the result must be something like this > > Cod Product date > 10 Nissan Car 2003-08-10 > 20 Guitar Yamaha 2003-10-05 > 30 Stereo Cables 2003-01-20 That really doesn't need a SubSelect. To simplify things, I created table Q1 with data as follows: +------+---------------+------------+ | Cod | Product | ItemDate | +------+---------------+------------+ | 10 | Nissan Car | 2003-06-20 | | 20 | Guitar Yamaha | 2003-07-01 | | 10 | Nissan Car | 2003-05-01 | | 30 | Stereo Cables | 2003-01-20 | | 20 | Guitar Yamaha | 2003-10-05 | | 10 | Nissan Car | 2003-08-10 | | 10 | Nissan Car | 2003-07-20 | | 20 | Guitar Yamaha | 2003-09-10 | +------+---------------+------------+ This matches the data you list above. The following query: Select Cod, Product, Max(ItemDate) from Q1 Group By Cod; Returns +------+---------------+---------------+ | Cod | Product | Max(itemdate) | +------+---------------+---------------+ | 10 | Nissan Car | 2003-08-10 | | 20 | Guitar Yamaha | 2003-10-05 | | 30 | Stereo Cables | 2003-01-20 | +------+---------------+---------------+ Which appears to be what your're looking for. This should work in any version of MySQL. - ---Michael -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQE/HKc5jeziQOokQnARAq11AJkB60/rV+CylqxaK/qSa0Gj5Z5hzwCeONV6 hAuU4NW9+HOajJpenIdA5BY= =unNS -----END PGP SIGNATURE----- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
