Hi,
On Wed, 20 Jun 2001, Benjamin Pflugmann wrote:
> > This is my sql statement:
> > mysql> select count(*),travel.nome_hotel,travel.tour_operator,
> > city.city,travel.vitofferta from travel,city where city.id=travel.cityid
> > group by travel.nome_hotel limit 5;
> >
> > The result:
> > +----------+-------------------+----------------+---------------+------------+
> > | count(*) | nome_hotel | tour_operator | city | vitofferta |
> > +----------+-------------------+----------------+---------------+------------+
> > | 2 | Abi d'Oru | HELVETIC TOURS | Sardegna Nord | 1995 |
> > | 2 | Abou Nawas Djerba | HELVETIC TOURS | Jerba | 899 |
> > | 9 | Adams Beach | HELVETIC TOURS | Cipro | 1095 |
> > | 1 | Aegean Village | HELVETIC TOURS | Cos | 1099 |
> > | 2 | Aeneas | HELVETIC TOURS | Cipro | 1211 |
> > +----------+-------------------+----------------+---------------+------------+
> >
> > Perfect! But it the price the last inserted price?? I am not sure. Here
> > I remove the periodo field as it wraps.
> >
> > Can you tell me how to do this to create the hotel table?
>
> There is no nice one-query solution to this problem with MySQL, but
> only a (speed-inefficient) hack. See the tutorial in the manual for
> more details:
>
> http://www.mysql.com/doc/e/x/example-Maximum-column-group-row.html
Actually I did something like this
mysql> select max(id) as id,nome_hotel,tour_operator,max(periodo) as
periodo,vitofferta from travel group by nome_hotel limit 10;
+------+-------------------+----------------+------------+------------+
| id | nome_hotel | tour_operator | periodo | vitofferta |
+------+-------------------+----------------+------------+------------+
| 1331 | Abi d'Oru | HELVETIC TOURS | 2001-07-06 | 1995 |
| 1218 | Abou Nawas Djerba | HELVETIC TOURS | 2001-07-04 | 899 |
| 1252 | Adams Beach | HELVETIC TOURS | 2001-06-30 | 1095 |
| 1058 | Aegean Village | HELVETIC TOURS | 2001-07-07 | 1099 |
| 1136 | Aegeon | HELVETIC TOURS | 2001-06-27 | 449 |
| 291 | Aeneas | HELVETIC TOURS | 2001-06-23 | 1211 |
| 949 | Aeolos | HELVETIC TOURS | 2001-07-05 | 799 |
| 986 | Agrilia | HELVETIC TOURS | 2001-06-29 | 349 |
| 1325 | Airone | HELVETIC TOURS | 2001-07-06 | 995 |
| 931 | Albatros | HELVETIC TOURS | 2001-07-06 | 399 |
+------+-------------------+----------------+------------+------------+
10 rows in set (0.08 sec)
But again the vitofferta (price) reflects to the first row when
counted. This is wrong I need it to take the price selected from the
max(period) or max(id).
Adrian
---------------------------------------------------------------------
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
