9
select * from dc_deal group by collection_id

In collection_id column i have values (1,3,3,4,4,5,NULL,NULL). Above query will return rows with (1,2,3,4,NULL) but i want to skip grouping on NULL value and need result like (1,2,3,4,NULL,NULL)

Wasim A.
  • 9,660
  • 22
  • 90
  • 120
  • You could use a union, with the first grouped select constrained by `where collection_id is not null` and the second, ungrouped, one constrained by `where collection_id is null` – barry-johnson Mar 13 '14 at 04:31
  • good opnion but need even more efficient query, i need to extend filter on query even more, so union will make it slower – Wasim A. Mar 13 '14 at 04:33
  • using union, i can't even order by started_date etc – Wasim A. Mar 13 '14 at 04:35
  • Could you show the table structure? – Raging Bull Mar 13 '14 at 04:41
  • 1
    re: 'using union, i can't even order by started_date etc' - I didn't see `started_date` in your query above. I understand not wanting to post your entire query, but at least something that conveys actually what you want to do saves everybody time. – barry-johnson Mar 13 '14 at 04:45
  • It's not necessary to use UNION or UNION ALL. You can add another expression in the GROUP BY clause. (Note that the UNION operation eliminates duplicate rows; if you don't need to do that, then for better performance, use a `UNION ALL` operator instead, to avoid the overhead of the sort required to remove duplicates. Note that it is possible to use `ORDER BY` with a `UNION` or `UNION ALL` query, but that can only reference columns/expressions in the SELECT list of the query, and those references have to be by column name or ordinal position in the result set. – spencer7593 Mar 13 '14 at 04:58

3 Answers3

13

If we have a unique column (or set of columns) in the table, then we can add another expression to the GROUP BY.

The expression needs to return a unique value for each row when collection_id is null. Otherwise, it returns a constant.

Assuming we have a unique id column in the table, then we can do something like this:

... GROUP BY collection_id, IF(collection_id IS NULL, id, 0)

That second expression in the GROUP BY returns a constant value 0 when collection_id is not null. But it returns a unique value for each row when collection_id is null.

Note that id here is just a reference to a column that is defined to be unique within the table. The PRIMARY KEY is a good candidate. If we don't have a unique index on a single column, then we can repeat this same type of expression for each column in our unique constraint, or for any set of expressions that is guaranteed to be unique on each row.

... GROUP BY collection_id
           , IF(collection_id IS NULL, col1, '')
           , IF(collection_id IS NULL, col2, NULL)
           , IF(collection_id IS NULL, col3, collection_id)

Alternatively, we can use an expression generates a unique value:

... GROUP BY IFNULL(collection_id,UUID())
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • i don't understand IF(collection_id IS NULL, id, 0) after group by, what is its relation etc with group by – Wasim A. Mar 13 '14 at 04:55
  • @Wasim: The `GROUP BY` clause includes a comma separated list of expressions. You can `GROUP BY` more than one column. For example, you could "`GROUP BY collection_id, purchase_year`", and get back rows that have the same collection_id on multiple rows, but for each value of collection_id, the values of purchase_year will be different. The "trick" to getting the result you want is to use an expression that returns either a unique or constant value, based on a condition. – spencer7593 Mar 13 '14 at 05:17
  • `IFNULL(collection_id, 0)` can be shorter :) – Timo Huovinen Jul 18 '14 at 10:06
  • @TimoHuovinen: Yes, it's true that `IFNULL(collection_id,0)` is a shorter expression. But unfortunately that shorter expression doesn't provide any help in returning the specified resultset. In order to return the result OP requested, we need to return a **UNIQUE value** on each row where `collection_id` is NULL. So, returning a constant zero in place of a constant NULL doesn't help. – spencer7593 Jun 26 '15 at 01:33
  • 2
    GROUP BY IFNULL(collection_id, UUID()) – EthraZa Sep 13 '16 at 19:26
4

Try this:

SELECT * FROM dc_deal 
GROUP BY collection_id, 
case WHEN collection_id IS NULL THEN ID ELSE 0 END

Replace ID with another column in the table to group by.

See an example in SQL Fiddle.

Raging Bull
  • 18,593
  • 13
  • 50
  • 55
0

Try This :
select * from dc_deal where collection_id IS NOT NUll group by collection_id

MySQL SELECT only not null values

Sagar
  • 198
  • 2
  • 11