Problem with my query

Databases
1

Hi all,

I can’t figure out what’s wrong with this query. Can you help?

SELECT tid, uid, points, FROM_UNIXTIME(time_stamp),
FROM userpoints_txn
WHERE YEAR(FROM_UNIXTIME(time_stamp)) = 2010
AND MONTH(FROM_UNIXTIME(time_stamp)) = 5
AND description LIKE ‘%inactivity%’;

2

remove the comma after the last column in the select list

3

kmillecam, a little tip about the layout of queries, if you lay them out like below (using your own one as an example), it doesn’t run any faster or slower but it makes it easier to spot mistakes like stray , and you’ll find queries more readable, especially when you have more complex queries involving joins and sub-queries.

SELECT
      tid
    , uid
    , points
    , FROM_UNIXTIME(time_stamp)
FROM
    userpoints_txn
WHERE
    YEAR(FROM_UNIXTIME(time_stamp)) = 2010
AND
    MONTH(FROM_UNIXTIME(time_stamp)) = 5
AND
    description LIKE '%inactivity%'
4

Thank you both so much for your help with this issue!

I appreciate your willingness to help (and teach) those of us who struggle with these concepts.

Kevin

5

fascinating

you have the WHERE clause connectors on separate lines, but not the SELECT clause connectors

although i must admit i like your leading commas

leading ANDs and ORs work just as well

:wink:

6

kmillecam, the way you are isolating the rows for a particular month will work correctly but can potentially be very slow

assuming there’s an index on the time_stamp column, this will be a lot faster…


WHERE time_stamp >= UNIX_TIMESTAMP('2010-05-01')
  AND time_stamp  < UNIX_TIMESTAMP('2010-06-01')