How can I modify this query to fit my needs?
SELECT EMAIL, USERNAME, COUNT(*) AS C FROM REVIEWS GROUP BY EMAIL ORDER
BY C DESC
EMAIL may not be NULL, USERNAME may be NULL.
Example of rows in table REVIEWS:
EMAIL, USERNAME, CREATED
"someone@somewhere.com", NULL, 2004-12-01
"anyone@anywhere.com", "Anyone", 2004-12-01
"someone@somewhere.com", "Someone", 2004-12-11
"someone@somewhere.com", "SOMEONE", 2004-12-13
"someone@somewhere.com", NULL, 2004-12-16
Result of the query:
"someone@somewhere.com", NULL, 3
"anyone@anywhere.com", "Anyone", 1
I would like the result to be:
"someone@somewhere.com", "SOMEONE", 3
"anyone@anywhere.com", "Anyone", 1
since the latest not null value for the user with email
someone@somewhere.com is "SOMEONE".
Is that possible?
Leif
Albert Grein?cker - 29 Dec 2004 16:50 GMT
I hope I understood your question right...
maybe isnull could solve your problem, which means...
SELECT EMAIL, USERNAME, COUNT(*) AS C FROM REVIEWS where not isnull(email)
GROUP BY EMAIL ORDER BY C DESC
Albert
> How can I modify this query to fit my needs?
>
[quoted text clipped - 28 lines]
>
> Leif