How to get parent's most viewed child in MySQL


Problem :

I have a catalogue categories with items. The main catalogue page must display the list of categories with the amount of items inside of each category, and the "preview" picture must be the most viewed item of that same category.

catalogue_cat

  • id_cat
  • name

catalogue_item

  • id_item
  • name
  • picture_url
  • views (INT)

    SELECT catalogue_cat.id_cat, catalogue_cat.name, COUNT(catalogue_item.id_item) AS q FROM catalogue_cat LEFT JOIN catalogue_item ON catalogue_item.id_cat = catalogue_cat.id_cat GROUP BY catalogue_cat.id_cat ORDER BY catalogue_cat.name

That only brings me the list of categories with the item count. I still need the picture of the most viewed item of each category. Any ideas? Thanks!



Solution :

You are already doing a group by, so the best approach is the substring_index()/group by approach:

SELECT c.id_cat, c.name, COUNT(i.id_item) AS q,
       SUBSTRING_INDEX(GROUP_CONCAT(i.url ORDER BY i.views DESC), ',', 1) as MostViewedURL
FROM catalogue_cat c LEFT JOIN
     catalogue_item ci
     ON i.id_cat = c.id_cat
GROUP BY c.id_cat
ORDER BY c.name;

Note that this assumes that the URL's don't have commas in them. If they do, you will need to find another separator character.


Mysql Tutorials

Mysql Howto..

How to display an SQL string that has been constructed by MySQLDB cursor?

How to write table.column in MySQL with PHP?

If I specify any value for an auto-increment field, how can I get the next or previous value?

How to concatenate and Alter/Update table [duplicate]

How to select unique values in MySQL and order by another field

How to group values of columns using PHP MySQL?

How to find duplicate Input With MySQL?

PHP: How to insert array value into mysql statement

How to organize model classes and related database data for geographical spaces?

how to write this mysql join

How do I select by a range of starting characters?

how to use lampp with external mysql

How to add information to MySQL database using java code

PHP/MySQL: How to save media player playlist order

how to hide smth with PHP if there is a specific variable or word in the list

How to build MySQL connector c++ with VS 2010 on a Windows x64 machine?

MSSQL to MySQL migration - char encoding issues with UCS-2 surrogate pairs, how can I remove these from MSSQL database?

Mysql how to sort by firstletter in first name and first letter in lastname

How to import “a lot” of data to MySQL with PHP and foreign keys?

how to extract table names from mysql query with php?

How to handle Multi-Threaded requests to mysql to get a free row?

How to provide a download of a mySQL database in PHP

How to Bind MySql Database inside a JAR file of any application?

How to query posts by all friends of a user (stored between 3 MySQL tables)?

How to check after update on maximum constraints?

How to access EngineYard database from local machine?

How to change the storage engine type on MySQL?

How to count the probability and update the calculation value to the original table using MySQL?

MySQL: how to use comma, single quote, and double quote as columns?

How to convert a string to variable through a loop and save it in MySQL Database