Group by two values

Question

I have the following query:

SELECT
    items.*
FROM
    `items` 
INNER JOIN
    `users` ON `items`.`owner` = `users`.`id` 
GROUP BY
    `items`.`owner` 
LIMIT
    10

I ensures it is grouped by the user (only one item fetched per user), but I also wish ensure that items with the category, say, "1" only appears once.

But that does not work. Well, query succeeds, but it does not group by category. Multiple categories is still shown. Any ideas?

I have created a SQLFiddle here: http://sqlfiddle.com/#!2/0a4bad/1

Instead of outputting:

+----+----------+-------+
| ID | CATEGORY | OWNER |
+----+----------+-------+
|  1 |        1 |     1 |
|  2 |        1 |     2 |
|  3 |        1 |     3 |
|  4 |        2 |     4 |
|  5 |        2 |     5 |
+----+----------+-------+

It should be outputting:

+----+----------+-------+
| ID | CATEGORY | OWNER |
+----+----------+-------+
|  1 |        1 |     1 |
|  4 |        2 |     2 |
|  5 |        2 |     4 |
|  5 |        2 |     5 |
|  8 |        3 |     3 |
+----+----------+-------+

(notice category 1 is only shown ONCE).

I want to ensure that only one item per owner is shown, and then adtionally ensure that a specific category (say 1 and 5) is only shown once. The category 1 and 5 are overpopulated, and if they are not limited, they will be 90% of the output.

It looks fine to me. When you say "it doesnt work" what does that mean? Do you get an error, if so what is it? — crthompson, Dec 17 '14 at 17:16
possible duplicate of [Is it possible to GROUP BY multiple columns using MySQL?](http://stackoverflow.com/questions/1841426/is-it-possible-to-group-by-multiple-columns-using-mysql) — crthompson, Dec 17 '14 at 17:17
It doesn't look fine to me. Selecting * with a group by clause never looks fine to me. Neither does a limit clause without an order by clause. — Dan Bracuk, Dec 17 '14 at 17:19
@Strawberry, my point was to get OP to post their error (it looks fine to me syntactically, not logically). Dan's point was that its not standard sql and ought to be re-thought. Both valid IMO. — crthompson, Dec 17 '14 at 17:30
I see, what I initally wrote was confusing. I have updated my question, alongside with a SQLFiddle: http://sqlfiddle.com/#!2/0a4bad/1 — FooBar, Dec 17 '14 at 17:43
Now, i'm a bit more confused. You want to see unique owners, but also want to see unique categories. Which owners do you want to *not* show? — crthompson, Dec 17 '14 at 18:05
The fiddle is great, but perhaps you can add to your question what your expected output should be? — crthompson, Dec 17 '14 at 18:06
I want to ensure that only one item per owner is shown, and then adtionally ensure that a specific category (say 1 and 5) is only shown once. The category 1 and 5 are overpopulated, and if they are not limited, they will be 90% of the output. — FooBar, Dec 17 '14 at 18:14
Lets take this [conversation to chat](https://chat.stackoverflow.com/rooms/67150/group-by-two-values) — crthompson, Dec 17 '14 at 18:22
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/67152/discussion-between-paqogomez-and-mattias). — crthompson, Dec 17 '14 at 18:28
Where does 4,2,2 come from? And each category appears to be exactly as populous as any other. Frankly, I have no idea what's going on here. Other than that, it looks fine to me. — Strawberry, Dec 18 '14 at 00:44

score 0 · Answer 1 · answered Dec 17 '14 at 17:19

0

You can use DISTINCT to retrieve unique data:

SELECT DISTINCT items.category

answered Dec 17 '14 at 17:19

Cerveser

752
8
23

FuzzyTree · Answer 2 · 2014-12-17T20:12:34.430

0

select * from items t1
where category not in (1,2)
or not exists (
    select 1 from items t2
    where t2.id < t1.id
    and t2.category = t1.category
)
group by owner

http://sqlfiddle.com/#!2/0a4bad/27

edited Dec 17 '14 at 20:12

answered Dec 17 '14 at 19:41

FuzzyTree

32,014
3
54
85

Thanks for the try, but the two issues: 1. Not all results are being shown (see for instance: http://sqlfiddle.com/#!2/0a4bad/22) 2. In the real query, I need to order by a specific `distance` field (currently you are ordering by category). – FooBar Dec 17 '14 at 19:54
@Mattias see update, although I don't this query will not guarantee the results you want because mysql randomly selects a category since you're only grouping by owner – FuzzyTree Dec 17 '14 at 20:12

Group by two values

2 Answers2