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)
3 Answers
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())
5 Comments
Wasim A.
i don't understand IF(collection_id IS NULL, id, 0) after group by, what is its relation etc with group by
spencer7593
@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.
Timo Huovinen
IFNULL(collection_id, 0) can be shorter :)spencer7593
@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.
EthraZa
GROUP BY IFNULL(collection_id, UUID())
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.
Comments
Try This :
select * from dc_deal where collection_id IS NOT NUll group by collection_id
where collection_id is not nulland the second, ungrouped, one constrained bywhere collection_id is nullstarted_datein 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.