-----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]
