CODEWITHSUNDEEP
mysqlgroup-by
user3279320
user3279320

Reputation: 13

mysql group by ignore null

I have a table given below

id     album_id   album_name
----   ---------  -----------
1          5         test1
2          5         test1
3          6         test3
4          6         test3
5          NULL      test4
6          NULL      test4

I want to write a query using group by album_id , it will give below result

id     album_id   album_name
----   ---------  -----------
1          5         test1
3          6         test3
5          NULL      test4
6          NULL      test4

I have tried but its grouping NULL column also, I want all the rows which are NULL and group by album_id which are not null.

I have followed this link - but don't work for me

Thanks

Upvotes: 1

Views: 8110

Answers (3)

uvais
uvais

Reputation: 416

try this

 SELECT * FROM <tablename>
 GROUP BY album_id,id;

Upvotes: -1

GarethD
GarethD

Reputation: 69769

You could use this:

SELECT MIN(id) AS id,
       album_id,
       album_name
FROM T
GROUP BY COALESCE(album_id, id), album_name
ORDER BY album_name;

Example on SQL Fiddle

So when album_id IS NULL then you also group by id ensuring that all rows are returned where album_id is null.

Upvotes: 5

SriSaiKiran
SriSaiKiran

Reputation: 38

select * from YourTableName
group by album_id asc

try this..

Upvotes: -2

Related Questions