CODEWITHSUNDEEP
mysql
Kevin White
Kevin White

Reputation: 51

Query on rows where the elements of a specific field are not empty

I have the following MySQL query

SELECT field1,
COUNT(DISTINCT field2) AS numbercount, 
MAX(ADDTIME(`date`,`start`)) AS `datetime` FROM mydatabase GROUP BY field1 
ORDER BY numbercount DESC, `datetime` ASC LIMIT 0,11

but my results aren't like I want. I need to consider only rows where "field1" has any value and is not empty.

Suggestions?

EDIT: meanwhile I tested the following solution ( adding WHERE NOT LIKE '') and it seems to be ok:

SELECT field1, COUNT(DISTINCT field2) AS numbercount,
MAX(ADDTIME(date,start)) AS datetime FROM mydatabase WHERE NOT LIKE '' GROUP BY field1
ORDER BY numbercount DESC, datetime ASC LIMIT 0,11

Are there better solutions?

Upvotes: 0

Views: 24

Answers (1)

user9081948
user9081948

Reputation:

You need to add field is not null and empty statement.

SELECT field1,
COUNT(DISTINCT field2) AS numbercount, 
MAX(ADDTIME(`date`,`start`)) AS `datetime`
FROM mydatabase WHERE field1<>null AND TRIM(field1) <> ‘’ GROUP BY field1 
ORDER BY numbercount DESC, `datetime` ASC LIMIT 0,11

Upvotes: 2

Related Questions