How to display the three last article from each category?

Question

I'm having a table Content with following structure,

Id | title | category | content | created_at

At least it's contained more than one category and lot of article for each category.

I want to display the three last article of each category on my index page. How can I do that using a single SQL query ?

hi, you can use a row_number partitioned by category order by desc and then select all the rows where rownumber <= 3. — F.Lazarescu, Jul 01 '19 at 07:00
Try using `substring_index` to get article from each category. — Shihas, Jul 01 '19 at 07:39

CodeBreaker · Answer 1 · 2019-07-01T09:25:52.960

0

You can select data with limit of 3 and in descending order.so it will gives you the last three record.Here i have already assume that you have another table rather than Content table named Article which have all the data of article along with Content table id as foreign key reference.

select 
    * 
    from 
    `article` where `category_id`='$category_id'
    order by 
     id desc
    limit 3

edited Jul 01 '19 at 09:25

answered Jul 01 '19 at 07:30

CodeBreaker

395
2
9

How does this work to limit the last three articles from each category? – Raymond Nijland Jul 01 '19 at 08:09
You will only have the last 3 articles,, what about the "from each category" part ? – Mickaël Leger Jul 01 '19 at 08:13
see my edited answer. i forgot that condition – CodeBreaker Jul 01 '19 at 09:23

How to display the three last article from each category?

1 Answers1