Select one row where alert type and item id are the same and also how many similar rows

Question

I am trying to make the notification system for my project as simple as possible on the user's side.

"john brown and 4 others liked your photo"

I tried:

Select a.id, a.item_id, a.type u.f_name, u.l_name, count(a)
From alerts
Join users on a.user_id = u.u_id
Where owner_id = {THE_USER_ID}

but it doesnt "work"

The users table

u_id | f_name | l_name 
==========================
234    roy      wright    
654    pam      brown
564    kim      harris
334    tory     plummer
876    rick     forbes    
123    paul     nichol
980    mario    chang
454    todd     thompson
886    sam      richards
215    tash     gates
...    .....    ........

The alerts table

id   |   item_id   |   type    | user_id | owner_id
===================================================
1         21           like      234
2         21           comment   654
3         32           share     876
4         21           like      778
5         21           like      890
6         21           share     123
7         54           share     454
8         32           comment   655
9         60           comment   886
10        32           like      215
..        ..           ......    ...

The results I want

id | item_id | type   | u_id | f_name | l_name | amount_more 
============================================================
1    21       like     234    roy      wright    2
2    21       comment  654    pam      brown
3    32       comment  876    rick     forbes    
6    21       share    123    paul     nichol
7    54       share    454    todd     thompson
9    60       comment  886    sam      richards
10   32       like     215    tash     gates
..   ..       ....     ...    .....    ........

Add `GROUP BY a.id, a.item_id, a.type u.f_name, u.l_name` at the end. — jarlh, Dec 06 '16 at 15:19

Matt · Accepted Answer · 2016-12-06T17:25:40.053

0

SELECT
    MIN(CASE WHEN RowNumber = 1 THEN id END) as Id
    ,item_id
    ,type
    ,MIN(CASE WHEN RowNumber = 1 THEN u_id END) as u_id
    ,MIN(CASE WHEN RowNumber = 1 THEN f_name END) as f_name
    ,MIN(CASE WHEN RowNumber = 1 THEN l_name END) as l_name
    ,COUNT(*) - 1 as amount_more
FROM
  (
    SELECT
        t.*
        ,(@rn:= if((@item = t.item_id) AND (@type = t.type),@rn + 1,
                   if((@item:=t.item_id) AND (@type:=t.type),1,1)
           )
        ) as RowNumber
    FROM
        (SELECT *
         FROM
            alerts a
            LEFT JOIN usersTbl u
            ON a.user_id = u.u_id
         WHERE
            a.owner_id = 201
         ORDER BY
             a.item_id
            ,a.type
            ,a.id) t
        CROSS JOIN (SELECT @rn:=0,@item:=0,@type:='') var
    ORDER BY
        item_id
        ,type
        ,id
   ) t2         
GROUP BY
    item_id
    ,type
ORDER BY
    id
;

So you need to create a partitioned row number to identify which record you want to be considered first. Then using conditional aggregation you can choose that as your User. this works check it out here: http://rextester.com/VXZONO82847

edited Dec 06 '16 at 17:25

answered Dec 06 '16 at 16:40

Matt

13,833
2
16
28

Hi @matt I do apologize, the record with amount 2 for rick forbes shouldn't be there, i'll edit the question, thanks for your answer I am about to test it – Cheyenne Forbes Dec 06 '16 at 17:01
Hi @matt it works perfect!! do you do paid or free checking of sql schemas and queries? – Cheyenne Forbes Dec 06 '16 at 17:26
@CheyenneForbesdeAvapno I work in the field full time but have previously consulted and am open to consulting and other paid opportunities. I would be glad to contact you to discuss further if desired. I did find a gmail address on what I assume is the support page of your website please let me know if that is the best way of contacting. – Matt Dec 06 '16 at 17:59
cheyenne.osanu.forbes@gmail.com – Cheyenne Forbes Dec 06 '16 at 18:25

Adarsh Gangadharan · Answer 2 · 2016-12-06T16:30:28.833

-1

In the Query you are not using aliases a and u properly, Use this:

Select 
a.id, 
a.item_id, 
a.type,
u.f_name, 
u.l_name, 
count(a.*)
From alerts a Join users u
on(a.user_id = u.u_id)
Where owner_id = {THE_USER_ID}
group by 1,2,3,4,5;

edited Dec 06 '16 at 16:30

answered Dec 06 '16 at 15:20

Adarsh Gangadharan

653
4
10

No `GROUP BY` needed? – jarlh Dec 06 '16 at 15:22
will this give me: `1 | 21 | like | 234 | roy | wright | 2` as shown in "The results I want" – Cheyenne Forbes Dec 06 '16 at 15:29
Thanks jarlh, group by is required. – Adarsh Gangadharan Dec 06 '16 at 15:31
@CheyenneForbesdeAvapno no this wont give you the result table http://rextester.com/NIJ38596. and Adrash you have 2 syntax issues missing a comma after a.type and `count(a.*)` should just be `count(*)` or a particular column a.* doesn't work. – Matt Dec 06 '16 at 16:09
@Matt it looks like the sql he is trying and the result set he want is diverging. Cheyenne, you can use the sql i shared above (syntax errors apart) and then think about the output what you want. – Adarsh Gangadharan Dec 06 '16 at 16:43
@AdarshGangadharan the sql in the OP is what he attempted not necessarily what he needed. Typically posters show this to show that they have at least tried something so people are more willing to work on their issue. The result set is typically the most important thing to pay attention to. In this case he is choosing a ranking function to determine a user to tout as the first person to like the photo then also wants to present how many others liked it. To accomplish this you will need to rank user per item_id and type then aggregate/present as desired see my answer – Matt Dec 06 '16 at 16:49

Select one row where alert type and item id are the same and also how many similar rows

2 Answers2