MySQL Select COUNT(*) and Row with Max Date -

i trying select count(*) of deals grouped seller along recent product , date added. however, reason seems keep ordering deals creation date, ascending when try subqueries prevent that. here example table:

------------------------------------------------ | id | provider |      url        | createdate | ------------------------------------------------ | 1  | prov1    | http://ex.com/1 | 2015-05-10 | | 2  | prov1    | http://ex.com/2 | 2015-06-10 | | 3  | prov1    | http://ex.com/3 | 2015-07-10 | | 4  | prov2    | http://ex.com/4 | 2015-05-10 | | 5  | prov2    | http://ex.com/5 | 2015-06-10 | ------------------------------------------------ 

i looking return following:

----------------------------------------------------------- | id | count(*) | provider |      url        | createdate | ----------------------------------------------------------- | 3  |    3     | prov1    | http://ex.com/3 | 2015-07-10 | | 5  |    2     | prov2    | http://ex.com/5 | 2015-06-10 | ----------------------------------------------------------- 

my current query is:

select id,count(*),provider,createdate,url (select * products order createdate desc) group provider; 

but doesn't seem work. have suggestion?

edit thank great answers. extremely strange me while seem work in sql fiddle, fail on database server. example using following on server provides following:

mysql> insert products -> (`id`, `provider`, `url`, `createdate`) -> values -> (1, 'prov1', 'http://ex.com/1','2015-05-10'), -> (2, 'prov1', 'http://ex.com/2','2015-06-10'), -> (3, 'prov1', 'http://ex.com/3','2015-07-10'), -> (4, 'prov2', 'http://ex.com/4','2015-05-10'), -> (5, 'prov2', 'http://ex.com/5','2015-06-10') -> ; query ok, 5 rows affected (0.06 sec) records: 5  duplicates: 0  warnings: 0  mysql> select id,count(*),provider,createdate,url     -> (select * products order createdate desc) t1     -> group provider; +------+----------+----------+---------------------+-----------------+ | id   | count(*) | provider | createdate          | url             | +------+----------+----------+---------------------+-----------------+ |    1 |        3 | prov1    | 2015-05-10 00:00:00 | http://ex.com/1 | |    4 |        2 | prov2    | 2015-05-10 00:00:00 | http://ex.com/4 | +------+----------+----------+---------------------+-----------------+ 

while running same thing on sql fiddle works expected. further comment regarding join should work, having same issue returning unexpected results. version of mysql 5.5.37-mariadb-34.0. ideas on this?

select p1.*, count(*) products p1 join (     select provider, max(createdate) m_date     products      group provider ) p2 on p1.provider = p2.provider , p1.createdate = p2.m_date