The Real Hero
The Real Hero

Reputation: 47

display last 2 entries in each category from a mysql table

I have a table like following

===============================================
id          |      category    |    names     |
===============================================
1           |          A       |    name1     |
2           |          A       |    name2     |
3           |          A       |    name3     |
4           |          B       |    name4     |
5           |          B       |    name5     |
6           |          B       |    name6     |
7           |          B       |    name7     |
8           |          C       |    name8     |

Expected output: name8, name7,name6,name3,name2

I wish to display last 2 entries in each category is that possible? Someone Please help me. Thanks in advace.

Upvotes: 3

Views: 830

Answers (3)

Naincy
Naincy

Reputation: 2943

$query =   SELECT * from table_name Order By id DESC limit 2;

By using ORDER BY ... DESC it will make select data from table in descending order.

// execute query 
while () // fetch rows
{
    $name_arr[] = $row['names']; 
}

$output = implode(",", $name_arr);
echo $ouput;

Upvotes: -2

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

These type of results are best handled by window functions in other RDBMS but unfortunately Mysql don't have any window functions so for alternative there is a solution to use user defined variables to assign a rank for rows that belong to same group

SELECT  `id`, `category`, `names`
FROM (
SELECT *,
@r:= CASE WHEN @g = category THEN @r + 1 ELSE 1 END rownum,
@g:=category
FROM test
  CROSS JOIN(SELECT @g:=NULL ,@r:=0) t
  ORDER BY category,id desc
) c
WHERE c.rownum <=2 

Above query will give you 2 recent records (on basis of id) per category you can change the last part of query with where clause to any number to show n results per group for example to show 3 records then WHERE c.rownum <= 3 and so on

Demo

Upvotes: 4

Fluffeh
Fluffeh

Reputation: 33532

It's quick and dirty, but this will probably work.

select
    cats.category,
    (select 
        a.id 
        from yourTable a 
        where a.category=cats.category 
        order by a.id desc limit 1,1) as SecondLast,
    (select 
        a.id 
        from yourTable a 
        where a.category=cats.category 
        order by a.id desc limit 0,1) as Last,
from
(select distinct category from yourTable) cats

It will return both last records in a single row however.

If you wanted to use both PHP and SQL, this becomes rather trivial however:

<?php
    $sql="select distinct category from yourTable";
    // stuff to run this query first.
    // Output placed into array called $array
    foreach($array as $cat)
    {
        $sql="select id, category, names from yourTable where category='$cat' oder by id desc limit 2";
        // Do stuff here to run this inner query.
        // Append results into array $finalArray
        $finalArray[]=$result;
    }

    print_r($finalArray);
?>

Upvotes: 0

Related Questions