CODEWITHSUNDEEP
mysql
Rod Nelson
Rod Nelson

Reputation: 3331

having problem with a query

I have a DB with user_id and last_updated

SELECT user_id, MAX(last_updated) as timestamp 
  FROM online 
 WHERE user_id > 0 
 GROUP_BY user_id

I'm getting this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP_BY user_id' at line 1

SELECT user_id, MAX(last_updated) as timestamp 
  FROM online 
 WHERE user_id > 0 
 GROUP_BY user_id

Someone suggested I use the above query so I'm not sure why I'm getting this error

I googled group_by and don't see what I did wrong. I thought maybe the as timestamp but not sure!

Upvotes: 0

Views: 56

Answers (3)

Jason McCreary
Jason McCreary

Reputation: 73031

It's GROUP BY not GROUP_BY...

SELECT user_id, MAX(last_updated) as timestamp 
  FROM online 
 WHERE user_id > 0 
 GROUP BY user_id

UPDATE

Per comments, also note that timestamp is a reserved word. You should chance the alias (i.e. max_last_updated) or escape it with backticks (i.e. `timestamp`)

Upvotes: 5

SIFE
SIFE

Reputation: 5715

timestamp is a data type used by MySQL, try to change it to some thing else.

Upvotes: 0

Johan
Johan

Reputation: 76753

Change the code from

SELECT user_id, MAX(last_updated) as timestamp 
FROM online WHERE user_id > 0 GROUP_BY user_id

to

SELECT user_id, MAX(last_updated) as `timestamp` 
FROM online WHERE user_id > 0 GROUP BY user_id

Reserved words (such as timestamp) can only be used a identifiers if enclosed by backticks '`'.

Also GROUP_BY -> GROUP BY *(Two words, SQL keywords never have a '_' in them)*.

Even better is to not use reserved words as they are confusing (and backticks make my head hurt)

Upvotes: 1

Related Questions