1

I am very new in MySQL, I have this table in mysql table

How can I produce a result like this?

What I want is to get the latest timestamp with a unique destination and year.

I have tried this mysql query but seems not to work:

SELECT chp_destination, cde_name, chp_year, chp_from, chp_to, chp_budget_price_high, chp_medium_price_high, chp_luxury_price_high,
    chp_budget_price_low, chp_medium_price_low, chp_luxury_price_low, chp_timestamp, chp_comment, MAX( chp_id ), 
FROM crm_hotel_price
LEFT JOIN crm_destinations ON cde_id = chp_destination
GROUP BY chp_destination, chp_year
ORDER BY chp_id
Community
  • 1
  • 1
mark yorky
  • 193
  • 1
  • 5
  • 17
  • You can find a very similar question, and a proper answer here: http://stackoverflow.com/questions/1181374/mysql-select-statement-distinct-for-multiple-columns – Itay Aug 14 '13 at 14:24
  • I took a look at that post question and i find it near to what i need except that it should also consider the latest datetime based on the unique destination and year. thanks by the way.. – mark yorky Aug 14 '13 at 14:34
  • "Seems not to work" is not an adequate description of the problem. Please put example data and results as text in your question. One of the goals of Stack Overflow is to become a searchable repository of solutions, and people can't search for text in in image on another site. Also, external links can expire unexpectedly. – Dour High Arch Aug 14 '13 at 15:21

1 Answers1

0

Try this: 

SELECT * 
FROM (SELECT chp_id, chp_destination, cde_name, chp_year, chp_from, chp_to, chp_budget_price_high, 
                 chp_medium_price_high, chp_luxury_price_high, chp_budget_price_low, chp_medium_price_low, 
                 chp_luxury_price_low, chp_timestamp, chp_comment
        FROM crm_hotel_price
        LEFT JOIN crm_destinations ON cde_id = chp_destination
        ORDER BY chp_destination, chp_year, chp_id DESC) A 
GROUP BY chp_destination, chp_year
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • I tried your query and it produced my desired result :). Anyway if I add the same unique destination and year, will it show the entry with the unique destination and year based on its datetime? Thanks by the way :) – mark yorky Aug 14 '13 at 14:38
  • also what does "A" mean? before GROUP BY ? could you please explain your query? thanks a lot man :) – mark yorky Aug 14 '13 at 14:44
  • Is there a simple way with this? – mark yorky Aug 14 '13 at 15:05