SQL - GROUP BY Clause

Question

I have a table in which I needed the latest 2 rows from each type of records. using GROUP BY.

For example, from the picture below, what I want is to get last 2 records depending date column from each page type. I know GROUP BY will be used but I am not quite familiar with that.. Thanks

Group by will not work for this case. You will need something like Yogesh's solution. — nicolasl, Oct 17 '18 at 16:11
i've used and got following error #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' — Syed Saqlain, Oct 17 '18 at 16:13
Time to upgrade. How old of a MySQL that it doesn't support all those basic features? — Eric, Oct 17 '18 at 16:15
database is on live server now.. can I upgrade this without removing my data ? @Eric — Syed Saqlain, Oct 17 '18 at 16:22
@Coffee You'll have to read the documentation. I'm not familiar with MySQL. — Eric, Oct 17 '18 at 16:23
@Coffee can you check if sessions variables work on your server version ? If they work, I can give it a try. Try: `set @a := 10; select @a;` and let me know if you get a result — Madhur Bhaiya, Oct 17 '18 at 16:48

nicolasl · Accepted Answer · 2018-10-17T18:58:52.337

1

Long story short, once you have so little different types, you could use an UNION aproach:

SELECT * FROM (SELECT * FROM table WHERE page = 'sometype' ORDER BY date limit 2) t
UNION ALL 
SELECT * FROM (SELECT * FROM table WHERE page = 'someothertype' ORDER BY date limit 2) t
UNION ALL 
SELECT * FROM (SELECT * FROM table WHERE page = 'otherothertype' ORDER BY date limit 2) t
...

Just repeat this for each "page" type you have.

If this solution doesn't fits you, take a look at this article: http://www.sqlines.com/mysql/how-to/get_top_n_each_group

Cheers

Nikao

edited Oct 17 '18 at 18:58

answered Oct 17 '18 at 16:52

nicolasl

421
3
16

Great ! just add (ORDER BY date DESC limit 2) . and it is giving the expected results.. Thanks alot. – Syed Saqlain Oct 17 '18 at 18:56
1

I overread the "latest" part of your question. Will update the answer ;) glad to help – nicolasl Oct 17 '18 at 18:58

Yogesh Sharma · Answer 2 · 2018-10-17T16:12:10.510

0

You can use correlated subquery with limit clause :

select t.*
from table t
where id in (select t1.id
             from table t1
             where t1.page = t.page
             order by t1.date desc
             limit 2
            );

However, only GROUP BY would not sufficient to do this you might need JOIN.

edited Oct 17 '18 at 16:12

answered Oct 17 '18 at 16:09

Yogesh Sharma

49,870
5
26
52

1

i got this error : #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – Syed Saqlain Oct 17 '18 at 16:11

score 0 · Answer 3 · answered Oct 17 '18 at 16:14

0

you can use row_number window function if your mysql version 8+ but if below then you may use below method of generating row number

set @num := 0, @page:= '';

select x.*
from (
   select t.*,
      @num := if(@page= page, @num + 1, 1) as row_number,
      @page:= page as d
  from table t
  order by date desc
) as x where x.row_number <= 2;

answered Oct 17 '18 at 16:14

Zaynul Abadin Tuhin

31,407
5
33
63

@Coffee please share your expected output – Zaynul Abadin Tuhin Oct 17 '18 at 16:23
It should show only 2 rows for each page name.. last 2 rows (with highest date, and one date less than highest ) 2 rows from blog, 2 rows from events, 2 rows from gallery – Syed Saqlain Oct 17 '18 at 16:28

score 0 · Answer 4 · answered Oct 17 '18 at 16:53

You can't use just GROUP BY to get the result you want. You can only do that with subqueries and GROUP_CONCAT. See this: Using LIMIT within GROUP BY to get N results per group?

If your MySQL version doesn't support these features you will need a different approach:

Get a list of all the different page types: SELECT DISTINCT page;
Programmatically iterate over the list of page types and SELECT ... LIMIT 2; from the main table

You should be aware that these two approaches might have very different performance if you have a huge database.

SQL - GROUP BY Clause

4 Answers4