List all 'feeds' and only 2 users with relationship

Question

I need to make a list of all the items in the table feed and show only the first 2 users who subscribe to the content, but I can not put together a query that does the list only 2 users limit 2.

I've tried N querys and subquery, but could not get the expected result. The nearest was using group_concat, but if it concatenates all users and does not allow limited only to two initial, and would have to usesubstring_index for this purpose.

_Query

select
  feed.id
, feed.type
, user.name

 from feed
 inner join user on user.id = feed.user
 group by feed.type

_Result

Array(
    [0] => Array(
            [id] => 1
            [type] => Comedy
            [name] => Mike
        )

    [1] => Array(
            [id] => 3
            [type] => War
            [name] => John
        )

    [2] => Array(
            [id] => 6
            [type] => Terror
            [name] => Sophia
        )
)

_Expected

Array(
    [0] => Array(
            [id] => 1
            [type] => Comedy
            [name] => Mike, Papa
        )

    [1] => Array(
            [id] => 3
            [type] => War
            [name] => John, Alex
        )

    [2] => Array(
            [id] => 6
            [type] => Terror
            [name] => Sophia, Jessica
        )
)

You need to limit your result set, this is a [greatest-n-per-group](http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) problem. — Clockwork-Muse, Aug 16 '15 at 14:13

score 0 · Answer 1 · answered Aug 16 '15 at 00:56

set @rn=0;
select id, type, name 
from 
(
select 
@rn:=@rn+1 AS r_n
,feed.id
,feed.type
,user.name
from feed
inner join user on user.id = feed.user
group by feed.id
order by feed.id) t
where t.r_n <= 2

You can generate row numbers per group and then select the first 2 rows per feed id.

FuzzyTree · Answer 2 · 2015-08-16T01:24:59.450

0

You can use variables to simulate row_number() to give each user per feed a "rank" and only select rows with number <= 2 before doing the grouping in order to only get 2 users per group:

select id, type, group_concat(name) from (
    select * from (
        select *, @rn := if(@prevFeedId = id, @rn+1, 1) as rn,
        @prevFeedId := id
        from (
            select
            feed.id
            , feed.type
            , user.name
            from feed
            inner join user on user.id = feed.user
        ) t1 order by id
    ) t1 where rn <= 2
) t1 group by id, type

edited Aug 16 '15 at 01:24

answered Aug 16 '15 at 00:58

FuzzyTree

32,014
3
54
85

thank you, there was an error: `Unknown column 'rn' in 'where clause' ` – Papa Charlie Aug 16 '15 at 01:16
Not yet, `group by id, type` not grouped, so I used `group by type` but it seems that `where rn <= 2` not work, appears a list of all users. – Papa Charlie Aug 16 '15 at 01:32

score 0 · Answer 3 · answered Aug 16 '15 at 00:58

0

I don't know exactly the schema of your tables but try the same approach you already tried with group_concat but join to a subquery like:

...
inner join 
(
  select user.id, user.name from user limit 2
) as x on x.id = feed.user
...

answered Aug 16 '15 at 00:58

Manny

69
2
10

When I use `limit 2`, list 2 feeds; `limit 3` list 3 feeds. Not limited only to users. – Papa Charlie Aug 16 '15 at 01:08

List all 'feeds' and only 2 users with relationship

3 Answers3