MySQL GROUP BY not empty rows

Question

I have a query which returns:

uid  | text    | flag | nonuid | another_id
123  | "tree"  | 1    | 176    | 7098
456  | "apple" | 1    | 321    | 9686
321  | "apple" | 0    |        | 5675
847  | "tree"  | 1    | 176    | 6456
993  | "car"   | 1    | 332    | 9686
222  | "cat"   | 0    |        | 7098
913  | "car"   | 0    |        | 2301
176  | "tree"  | 0    |        | 6456
982  | "car"   | 1    | 332    | 7098
332  | "car"   | 0    |        | 7321

uid: unique field for every record
text: not unique piece of text
flag: whenever this is a "repost" entry of another row
nonuid: uid of the original of the repost
another_id: process_id that created the entry

I need to "collapse this list, so that nonuid appears only once in the list. If the field is empty, then there is no repost, so the entire row should be displayed. So after running the "right" query I should see the same list like this:

uid  | text    | flag | nonuid | another_id: GROUP_CONCAT(another_id)
123  | "tree"  | 1    | 176    | 7098,6456
456  | "apple" | 1    | 321    | 9686
321  | "apple" | 0    |        | 5675
993  | "car"   | 1    | 332    | 9686,7098
222  | "cat"   | 0    |        | 7098
993  | "car"   | 0    |        | 9686
176  | "tree"  | 0    |        | 6456
332  | "car"   | 0    |        | 7321

I could GROUP BY the list by text, but uid 332 and uid 222 have same text, while both not being a repost - and I should keep the original and the repost. Alternatively I could collapse by nonuid, which also fails, since all the "empty" nonuid become grouped together.

why you need to select 993 car and not 982 car? is there any field that defines the order? — fthiella
– fthiella, Commented Feb 11, 2014 at 15:35
@fthiella Not really. As soon as there is only one nonuid field occurence and another_id is a group concat - it is fine. — Xeos
– Xeos, Commented Feb 11, 2014 at 15:43

Sirko · Accepted Answer · 2014-02-11 15:37:41Z

2

Why don't you just use GROUP BY on both columns you need?

SELECT `text`, `nonuid`, GROUP_CONCAT( `another_id`)
FROM yourTable
GROUP BY `text`, `nonuid`;

Example Fiddle

^{You have not specified, how to select the other columns, so I omitted them here}

answered Feb 11, 2014 at 15:37

Sirko

74.3k19 gold badges157 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ModulusJoe · Accepted Answer · 2014-02-11 15:39:21Z

1

It looks to me that you want to complete two queries, one containing all the entries which are original (flag=0) and a second where flag=1, which are then grouped by nonuid with the group concat, so you could create the quiers seperately and then union the two results.

SELECT * FROM table WHERE FLAG = 0
UNION
SELECT min(uid),text,flag,nonuid,GROUP_CONCAT(another_ID) 
    FROM table WHERE flag = 1 GROUP BY nonuid

Here min(uid) in the second query would give you the first repost uid. I don't think you specify which uid you wanted from the grouping.

answered Feb 11, 2014 at 15:39

ModulusJoe

1,45411 silver badges18 bronze badges

2 Comments

Xeos Over a year ago

This might work as good, but my original query has about 10 outer joins to fetch additional information. Duplicating those joins will be too expensive.

ModulusJoe Over a year ago

The next question is could you ever have two non-reposted entries that have the same text? In that instance the nonuid would be empty and the text would be the same and you would lose a row potentially?

Collectives™ on Stack Overflow

MySQL GROUP BY not empty rows

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related