0

I'm trying to get the most recent id for every company_id (as defined by the timestamp), I tried the below but it's multiple rows per company_id... 

SELECT id, MAX(created_timestamp), company_id
FROM company_status
WHERE active = 1
GROUP BY company_id, id

I can't do the GROUB BY statement without id because I get this error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lastone.company_status.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

nick
  • 3,521
  • 3
  • 22
  • 32
  • hmm I think replacing `id` with `MAX(id)` will do the job :), then just group by `company_id` – niceman May 05 '17 at 15:21
  • @niceman Isn't that assuming `id` is sequential or in order? – Honinbo Shusaku May 05 '17 at 15:22
  • You shouldn't have `id` in the `GROUP BY`, because then you get a different row for every ID. – Barmar May 05 '17 at 15:23
  • @Abdul yes that's true, aren't most `id`s like that ? – niceman May 05 '17 at 15:24
  • @niceman sorry, missed out the `MAX()`, the issue is that it's not sequential so can't use `MAX(id)`... – nick May 05 '17 at 15:24
  • @Barmar without `GROUP BY ..., id` I get: `Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lastone.company_status.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – nick May 05 '17 at 15:24
  • @Barmar please can you unmark this as a duplicate, the answer you pointed to doesn't help me. Thanks – nick May 05 '17 at 15:28
  • The second query in the first answer should work for you. – Barmar May 05 '17 at 15:31
  • 1
    If that doesn't work, you need to clarify this question to explain why. Show some sample data, the result you want, and the query you tried. It would be best if you included a link to sqlfiddle that demonstrates the problem. – Barmar May 05 '17 at 15:33
  • If you're still struggling, see http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query - but I strongly suspect that Barmar's link nails it. – Strawberry May 05 '17 at 15:49

0 Answers0