Find rows that have the same values in another column

sqlite

I have the following table.

User Permission John 1 Mary 1 Anne 1 Joe 2 Marie 2 Nick 3

I want to select and group users with the same permission number. Is this actually possible? I have millions of different permission values.

Expected output:

(John, Mary, Anne), (Joe, Marie), (Nick)

Best Answer

It's probably best to do this in the application, after all it's just presentation. Something like:

SELECT user, permission
FROM T
ORDER BY permission

then keep track of when permission changes.

However (untested), there seems to be a GROUP_CONCAT function that can do what you want:

SELECT GROUP_CONCAT(user), permission
FROM T
GROUP BY permission
ORDER BY permission

Related Solutions

How to show the related (suggested) keywords

I am assuming that your tags are stored in the "Tag" table, but if not it should be an easy fix for the below SQL. I have not used SQLite3 before, but this should be generic SQL. I used the literal "John," but just replace with a variable for whatever the user enters. Also, if you wanted to limit the count to at least x number then put in a having clause.

select t2.keyword
from TagsTree tt
join Tag t on tt.TagID = t.TagID
join TagsTree tt2 on tt.ImageID = tt2.ImageID
join Tag t2 on tt2.TagID = t2.TagID and t.TagID <> t2.TagID
where t.keyword = 'John'
group by t2.keyword
order by count(*) desc, t2.keyword

SQLite – How to Perform a Join Between Two Tables

I assume when you say JOIN you really mean a more general combination of the two tables. JOIN is an operator in SQL, so you should avoid using it when you are referring to a UNION. Anyhow, if I get it right you want a FULL OUTER JOIN between two tables:

select COALESCE(a.filename, b.filename) as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1
from T1 as a
FULL JOIN T2 as b
    ON a.filename = b.filename

COALESCE is a function that return the first value from left to right that is not null.

However, from what I understand SQLite only supports LEFT JOIN so you have to rewrite the query using two LEFT JOINS and a UNION as:

select a.filename as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1 
     , CASE WHEN b.filename IS NOT NULL -- a match
            THEN CASE WHEN (a.filesize, a.permissions) = (b.filesize, b.permissions)  
                      THEN 0 ELSE 1
                 END
       END     
from T1 as a
LEFT JOIN T2 as b
    ON a.filename = b.filename
UNION 
select b.filename as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1 
     , CASE WHEN a.filename IS NOT NULL -- a match
            THEN CASE WHEN (a.filesize, a.permissions) = (b.filesize, b.permissions)  
                      THEN 0 ELSE 1
                 END
       END     
from T2 as b
LEFT JOIN T1 as a
    ON a.filename = b.filename

Note that a and b changed place in the second leg of the union.

Best Answer

Related Solutions

How to show the related (suggested) keywords

SQLite – How to Perform a Join Between Two Tables

Related Question