CODEWITHSUNDEEP
mysqlsqlgroup-bygreatest-n-per-group
saman khademi
saman khademi

Reputation: 842

mysql select 2 row from each group by

i have 2 table with this structure

Products

id       title
-----------------
1       sample 1
2       sample 2
3       sample 3
4       sample 4
5       sample 5
6       sample 6

gallery

id       typeid       name
-------------------------------

1          1         sample for 1
2          1         sample for 1
3          1         sample for 1
4          2         sample for 2
5          2         sample for 2
7          2         sample for 2
8          3         sample for 3
9          3         sample for 3
10         3         sample for 3
11         4         sample for 4
12         4         sample for 4
13         5         sample for 5
14         5         sample for 5

and iwant this for lists of id eg(1,2,3)

id      typeid       name
---------------------

1      1          sample for 1
1      2          sample for 1
2      4          sample for 2
2      5          sample for 2
3      8          sample for 3
3      9          sample for 3

here is my query

 select p.*,g.* from products p inner join gallery g ON p.id=g.typeid where p.id in (3,4,5) group by typeid

here is real structure sqlfiddle link

Upvotes: 3

Views: 2221

Answers (2)

Ethan.R
Ethan.R

Reputation: 157

SELECT p.id,p.title, g.typeid, g.id, g.name
FROM products p 
INNER JOIN (SELECT * FROM gallery a
            WHERE (SELECT COUNT(*) FROM gallery b WHERE b.typeid = a.typeid AND b.id >= a.id)

please try this

Upvotes: 2

RRayasa
RRayasa

Reputation: 121

SELECT p.id, g.typeid, g.id, g.title
FROM products p 
INNER JOIN (SELECT * FROM gallery a
            WHERE (SELECT COUNT(*) FROM gallery b WHERE b.title = a.title AND b.id >= a.id) <= 2
           ) g ON p.id = g.typeid 
WHERE p.id in (3,4,5)

EDIT:

Try this SQL Fiddle Demo

SELECT p.id, g.typeid, g.id, g.name
FROM products p 
INNER JOIN (SELECT * FROM gallery a
            WHERE (SELECT COUNT(*) FROM gallery b WHERE b.typeid = a.typeid AND b.id >= a.id) <= 2
           ) g ON p.id = g.typeid 
WHERE p.id in (3,4,5) order by g.id asc

So basically this part

WHERE (SELECT COUNT(*) FROM gallery b WHERE b.title = a.title AND b.id >= a.id) <= 2

is used to replace group by typeid

Upvotes: 4

Related Questions