Reputation: 47
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
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
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
Upvotes: 4
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