Uncouth SQL

Wolfsoap

'SELECT date, tags, count(tags) as numberOfTags FROM journal WHERE date BETWEEN "1990-01-01" AND $now GROUP BY tags'

This works but doest come back with the right results, i was wondering why.

It come backs with the lits of tags and numbers, but it doesn't filter out the dates it should, it just returns every row, regardless of whether the date falls in the criteria or not.

Can someone tell me whats up with it please?

NogDog

What is $now? If a date string, you'll need single quotes around it just like the date before the and. (Or perhaps you could just replace it with the CURDATE() MySQL function?)

tomhath

What will happen depends on the DBMS. You are selecting date but not grouping by it. That doesn't make sense to me, but MySQL will let you do it, and it will return something (I forget what date you might get).

Is this returning dates prior to 1990? If so then I don't understand what's happening. If you want the results grouped by date then add it to the GROUP BY, otherwise remove it from the SELECT.

Sxooter

Wolfsoap wrote:
'SELECT date, tags, count(tags) as numberOfTags FROM journal WHERE date BETWEEN "1990-01-01" AND $now GROUP BY tags'

This works but doest come back with the right results, i was wondering why.

It come backs with the lits of tags and numbers, but it doesn't filter out the dates it should, it just returns every row, regardless of whether the date falls in the criteria or not.

Can someone tell me whats up with it please?

Suppose your data look like this:

date, tags
2001-01-01 abc
2001-01-31 abc

and you run the query above

Which date should you now get?

Yeah, I don't know either. It's why other dbs throw an error on that query because you can't be sure which date you're gonna get. If you want the highest date, then use max(date) in your select. If you want the lowest use min(date)