CODEWITHSUNDEEP
postgresql
LePerMu
LePerMu

Reputation: 97

How can I return groups of rows in Postgresql that have the same value in one column?

I'm trying to write a query that will return rows grouped together by same first name, where last name is LIKE "some search string %"

For example, if I searched for last name LIKE 'Smi%' I want to get this result back

{
 [{1, tofu_spice, Joe, Smith}, {2, jsmith, Joe, Smithy}],
 [{3, smirthy11, Jack, Smirth}, {5, jackal, Jack, Smiles}],
}
id username f_name l_name
1 tofu_spice Joe Smith
2 jsmith Joe Smithy
3 smirthy11 Jack Smirth
5 jackal Jack Smiles
6 kevs Jack Allie
7 rb2015 Rob Brown
8 luigi191 Rob Bran

where the first array in my example result has all the rows with same first name "Joe" and second array has all rows with same first name "Jack".

Is it possible to return such a result? I know GROUP BY is used with aggregate functions, but I don't want to perform any such operations. I just want all the rows for each group returned. The query I've come up with so far (which I know is wrong) is SELECT * FROM users WHERE l_name LIKE "Smi%" GROUP BY f_name

Upvotes: 1

Views: 2607

Answers (1)

jian
jian

Reputation: 4877

Manual reference: https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES

BEGIN;
CREATE temp TABLE users (
    id bigint,
    username text,
    f_name text,
    l_name text
);
INSERT INTO users
    VALUES (1, 'tofu_spice', 'Joe', 'Smith'),
    (2, 'jsmith', 'Joe', 'Smithy'),
    (3, 'smirthy11', 'Jack', 'Smirth'),
    (5, 'jackal', 'Jack', 'Smiles'),
    (6, 'kevs', 'Jack', 'Allie'),
    (7, 'rb2015', 'Rob', 'Brown'),
    (8, 'luigi191', 'Rob', 'Bran');
COMMIT;

SELECT
    array_agg(users.* ORDER BY id) AS grouped_user
FROM
    users
WHERE
    l_name LIKE 'Smi%'
GROUP BY
    f_name
HAVING
    cardinality(array_agg(users.* ORDER BY id)) > 1;

Upvotes: 3

Related Questions