0

How to show all data but limit the same name value only 2 rows to show for example, here is my table 'test':

id  name
1   title-1
2   title-1
3   title-2
4   title-1
5   title-3
6   title-2
7   title-1
8   title-2

and i want the output to get all data, but limit only 2 rows to show if it's same name value, like this:

id  name
4   title-1
7   title-1
6   title-2
8   title-2
5   title-3
Dimas Adi Andrea
  • 443
  • 3
  • 11
  • 25

3 Answers3

0

You can use row_number() in MySQL 8+:

select id, name
from (select t.*,
             row_number() over (partition by name order by id desc) as seqnum
      from t
     ) t
where sequm <= 2;

In earlier versions, you can use a correlated subquery:

select t.*
from t
where t.id >= coalesce( (select t2.id
                         from t t2
                         where t2.name = t.name
                         order by t2.id desc
                         limit 1, 1
                        ), t.id);

Here is a db<>fiddle showing that this produces the desired results.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Any idea for MySQL 5.6? – Dimas Adi Andrea May 13 '19 at 11:28
  • It's return error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'offset 1 limit 1 ) LIMIT 0, 25' at line 7 – Dimas Adi Andrea May 13 '19 at 11:33
  • It's said: #1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – Dimas Adi Andrea May 14 '19 at 01:21
  • @DimasAdiAndrea, does that mean your MySQL version IS actually MariaDB 10.0. – FanoFN May 14 '19 at 02:00
  • i've checked it, and it's said version 5.6 – Dimas Adi Andrea May 14 '19 at 02:01
  • I'm sorry. it's work but the result is not what i want. i want to get all data but only show 2 rows for the multiple name value. it's show all data – Dimas Adi Andrea May 14 '19 at 02:17
  • actually this question still related to this https://stackoverflow.com/questions/56111348/how-limit-only-2-rows-to-show-for-same-value-in-mysql/ – Dimas Adi Andrea May 14 '19 at 02:18
  • You are correct @DimasAdiAndrea . It is MySQL 5.6 engine. MariaDB is a basically MySQL but some operation are not the same (please correct me if I'm wrong ;). You can see from the Error message that you posted. Anyway, I've tested Gordon's solution and it does return exactly the same as your expected output. Only the ordering is not the same, which you can fix by adding `ORDER BY name` at the end. – FanoFN May 14 '19 at 03:41
  • @DimasAdiAndrea . . . It does not. Both queries do what you want. I've provided a SQL Fiddle with the data that you provide in your question. – Gordon Linoff May 14 '19 at 10:32
0

Try this. Must work.

SELECT name FROM test group by test having count(*) <= 2
sid
  • 365
  • 2
  • 11
0

Try this. 

SELECT name from (
SELECT name, row_number() over (partition by name) as RN from TEST group by test having count(Name) >= 1 ) a where RN <= 2
Jervs
  • 344
  • 1
  • 12