how to display most viewed post from each category?

Question

firstly i do not using this code for wordpress site , i want it code to put it into my own website.So now i have problem regarding that i want to display most viewed post from each of the category,

i have table => post

     content || category || views
   -------------------------------
     text 1   |  cat1    |  700
     text 2   |  cat2    |  900  <==== most views in cat 2
     text 3   |  cat2    |  900
     text 4   |  cat1    |  900  <==== most views in cat 1
     text 5   |  cat1    |  800
     text 6   |  cat2    |  800
     text 7   |  cat3    |  700  <==== most views in cat 3
     text 8   |  cat2    |  900
     text 9   |  cat3    |  100
     text10   |  cat1    |  100

i want output like this :

    content || category || views
   -------------------------------
     text 4   |  cat1    |  900
     text 2   |  cat2    |  900
     text 7   |  cat3    |  700

`max value per group` problem there are multiple methods to get the results this is a duplicated question https://stackoverflow.com/search?q=mysql+max+value+per+group — Raymond Nijland, Jul 07 '18 at 15:24

Tim Biegeleisen · Answer 1 · 2018-07-07T15:29:51.853

2

Join your table to a GROUP BY subquery which finds the most popular post for each category:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT category, MAX(views) AS max_views
    FROM yourTable
    GROUP BY category
) t2
    ON t1.category = t2.category AND t1.views = t2.max_views;

If a given category could have multiple posts tied with the same number of views, then you should ideally give us logic for how to break the tie. The above query would return all tied posts in such a case.

If you are using MySQL 8+, then we can use ROW_NUMBER (or maybe a RANK function):

SELECT
    content, category, views
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY views DESC) rn
    FROM yourTable
) t
WHERE rn = 1;

edited Jul 07 '18 at 15:29

answered Jul 07 '18 at 15:24

Tim Biegeleisen

502,043
27
286
360

thank you..... : ) – tarun Jul 07 '18 at 15:40
where did you this much about php and mysql . You have immediate solution to my queries..PLs tell me – tarun Jul 07 '18 at 15:40
1

@tarun After answering several thousand SQL questions on Stack Overflow, one gets fast at doing it. – Tim Biegeleisen Jul 07 '18 at 15:44
Don't know about its efficiency but I believe this will also work if you want to write a shorter query: `select * from post where (category,views) IN ( select category, max(views) from post group by category) group by category` @TimBiegeleisen If you can throw some light on this, it would be appreciated. – saibbyweb Jul 07 '18 at 15:52
but i also want to show all other rows in order of category .BASICALLY i dont want to use 'group by ' – tarun Jul 08 '18 at 10:02
@tarun Then take my first query and just use `ON t1.category = t2.category` alone as the join condition. You still need `GROUP BY`, because you are doing an aggregation. – Tim Biegeleisen Jul 08 '18 at 10:08
its not working..its showing all the results in table as it is – tarun Jul 08 '18 at 10:18
Open a new question. This thread has been abandoned. – Tim Biegeleisen Jul 08 '18 at 10:26
ok thanx all codes above – tarun Jul 08 '18 at 10:44
there is problem with this code , i have two 2 rows (ie. **text2** and **text3**) in cat2 with same views and unfortunetely they are the maximum views in that category .Now output is showing both of them inspite of one i want . i want it show one out of both as per the order of content column whichever orders first. – tarun Jul 08 '18 at 12:34
i have edit the table above for ur refrence – tarun Jul 08 '18 at 12:35

how to display most viewed post from each category?

1 Answers1