mysql - Select one of a row -

select      v.id,      v.photoid,      count(*) cnt,      p.photowidth,      p.dir  `views` v join `photos` p      on v.photoid = p.id group v.photoid desc  order cnt desc 

i have above mysql command. works great in photo table each photo linked directory id.

what want 1 row each dir id.

how can this?

you need remove other columns. need apply aggregate function column except directory id below. if need other column apply aggregate function columns in count. have used 1 instead of * improve performance of query.

 select      count(1) cnt,      p.dir  `photos` p   group p.dir desc  order cnt desc 

update:

so if photoid views on top following query helpful.

 select      p.id,      v.countrec cnt  `photos` p  join  (select photoid, count(1) countrec `views` group photoid) v     on p.id= v.photoid order cnt desc