Particular MySql Query

Question

Having the following tables

Post(*id, name, description, cat, publish_date)
Category(*id, name)

It is possible in ONE query to get (max) the first N element of each different category?

Assuming that N=3, i'd need the following result:

Result set:

["1", "Name1","Descr","cat1"]
["2", "Name1","Descr","cat1"]
["3", "Name1","Descr","cat1"]
["10","Name1","Descr","cat2"]
["20","Name1","Descr","cat2"]
["22","Name1","Descr","cat2"]
["25","Name1","Descr","cat3"]
["30","Name1","Descr","cat3"]
["19","Name1","Descr","cat3"]

And so on.

I need this, to get the first N article of EACH category, with one query (so without ask for a specific category but for all category in table)

It is possible? If yes what's the right query?

score 2 · Answer 1 · edited May 23 '17 at 12:29

2

You can use UNION to join multiple queries into one. This assumes that you know what type you are selecting for each set.

SELECT * FROM 
(
  SELECT * FROM T1 WHERE type='Type1' ORDER BY id DESC LIMIT 3
) DUMMY1

UNION ALL

SELECT * FROM 
(
  SELECT * FROM T1 WHERE type='Type2' ORDER BY id DESC LIMIT 3
) DUMMY2

UNION ALL

SELECT * FROM 
(
  SELECT * FROM T1 WHERE type='Type3' ORDER BY id DESC LIMIT 3
) DUMMY3

The DUMMY table aliases are needed to allow ordering within each subquery.

edited May 23 '17 at 12:29

Community

1
1

answered Jun 22 '15 at 16:02

degenerate

1,224
1
14
35

Type is stored in other table, there is a way to do this query with a loop or something to query for all types? – user2548436 Jun 22 '15 at 16:19
You can `JOIN` this table to the other table for each subquery. Ex: `SELECT * FROM T1 JOIN T2 ON T1.id=T2.id WHERE T2.type='Type3' ORDER BY T1.id DESC LIMIT 3` – degenerate Jun 22 '15 at 16:23
I'm sorry, this is not what I mean. I edit the post, please check the correction. Thank you very much for your help. – user2548436 Jun 22 '15 at 16:33

Alvaro Flaño Larrondo · Accepted Answer · 2015-06-23T13:04:35.480

2

This query will do what you need. If any category has less than 3 post it will still work.

SELECT P.id,P.name,P.description,C.name 
FROM Post P
LEFT JOIN Category C
ON P.type = C.id
WHERE FIND_IN_SET(P.id,
    (
        SELECT GROUP_CONCAT(ids) FROM
            (SELECT SUBSTRING_INDEX(GROUP_CONCAT(id),',',3) as ids
                FROM Post 
                GROUP BY type
            ) AS foo
        GROUP BY ''
    )
)

Here is a working SQL Fiddle

UPDATE

In response to your comment and updated question:

SELECT P.id,P.name,P.description,P.publish_date,C.name 
FROM Post P
LEFT JOIN Category C
ON P.type = C.id
WHERE FIND_IN_SET(P.id,
    (
        SELECT GROUP_CONCAT(ids) FROM
            (SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY publish_date DESC),',',3) as ids
                FROM Post
                GROUP BY type      
            ) AS foo
        GROUP BY ''
    )
)

edited Jun 23 '15 at 13:04

answered Jun 22 '15 at 16:40

Alvaro Flaño Larrondo

5,516
2
27
46

How does this query change if want to order post by date, so i can get the last 3 post of each category? – user2548436 Jun 23 '15 at 12:36
Where is the date column? In the `Post` table? – Alvaro Flaño Larrondo Jun 23 '15 at 12:40
Yes, the name is publish_date – user2548436 Jun 23 '15 at 12:46

Particular MySql Query

2 Answers2