Description:
Hi,
A peculiar problem.
I have table with date field.
The following are the queries and results.
From the result you will see that the second query is not yeilding the appropriate result.
Thanks in advance
Query 1- Without aggregate: working fine
mysql> select b.date_of_rating as mn, b.instrument_code,b.rating,b.ratings_watch from fitch_ratings_master b where b.instrument_code = 'BAL102' order by date_of_rating
-> \g
+------------+-----------------+--------------+---------------+
| mn | instrument_code | rating | ratings_watch |
+------------+-----------------+--------------+---------------+
| 2002-02-10 | BAL102 | F1(ind) | NONE |
| 2002-08-30 | BAL102 | F1+(ind)(CP) | NONE |
| 2002-11-28 | BAL102 | F1+(ind)(CP) | NONE |
| 2002-11-28 | BAL102 | F1+(ind) | NONE |
| 2003-03-19 | BAL102 | F1+(ind) | NONE |
| 2003-03-19 | BAL102 | F1+(ind) | NONE |
| 2003-08-14 | BAL102 | Withdrawn | NONE |<== This one
+------------+-----------------+--------------+---------------+
Second query - With aggregare problem
mysql> select max(date_of_rating) as mn, instrument_code,rating,ratings_watch from fitch_ratings_master where instrument_code = 'BAL102' group by instrument_code
-> \g
+------------+-----------------+----------+---------------+
| mn | instrument_code | rating | ratings_watch |
+------------+-----------------+----------+---------------+
| 2003-08-14 | BAL102 | F1+(ind) | NONE |
+------------+-----------------+----------+---------------+
1 row in set (0.00 sec)
How to repeat:
Create a table with a date field and try out the query.
I can send you the table with date if that will expedite things
Description: Hi, A peculiar problem. I have table with date field. The following are the queries and results. From the result you will see that the second query is not yeilding the appropriate result. Thanks in advance Query 1- Without aggregate: working fine mysql> select b.date_of_rating as mn, b.instrument_code,b.rating,b.ratings_watch from fitch_ratings_master b where b.instrument_code = 'BAL102' order by date_of_rating -> \g +------------+-----------------+--------------+---------------+ | mn | instrument_code | rating | ratings_watch | +------------+-----------------+--------------+---------------+ | 2002-02-10 | BAL102 | F1(ind) | NONE | | 2002-08-30 | BAL102 | F1+(ind)(CP) | NONE | | 2002-11-28 | BAL102 | F1+(ind)(CP) | NONE | | 2002-11-28 | BAL102 | F1+(ind) | NONE | | 2003-03-19 | BAL102 | F1+(ind) | NONE | | 2003-03-19 | BAL102 | F1+(ind) | NONE | | 2003-08-14 | BAL102 | Withdrawn | NONE |<== This one +------------+-----------------+--------------+---------------+ Second query - With aggregare problem mysql> select max(date_of_rating) as mn, instrument_code,rating,ratings_watch from fitch_ratings_master where instrument_code = 'BAL102' group by instrument_code -> \g +------------+-----------------+----------+---------------+ | mn | instrument_code | rating | ratings_watch | +------------+-----------------+----------+---------------+ | 2003-08-14 | BAL102 | F1+(ind) | NONE | +------------+-----------------+----------+---------------+ 1 row in set (0.00 sec) How to repeat: Create a table with a date field and try out the query. I can send you the table with date if that will expedite things