Sfrgttk

Method to test if a number is a perfect power?

How does Loki do this?

Would this custom Sorcerer variant that can only learn any verbal-component-only spell be unbalanced?

How do scammers retract money, while you can’t?

Closest Prime Number

Lay out the Carpet

Why are there no referendums in the US?

Is HostGator storing my password in plaintext?

Do sorcerers' Subtle Spells require a skill check to be unseen?

Crossing the line between justified force and brutality

Tiptoe or tiphoof? Adjusting words to better fit fantasy races

How to pronounce the slash sign

What is the opposite of 'gravitas'?

How do I find the solutions of the following equation?

Is there a korbon needed for conversion?

How did Doctor Strange see the winning outcome in Avengers: Infinity War?

Two monoidal structures and copowering

How long to clear the 'suck zone' of a turbofan after start is initiated?

Do the temporary hit points from the Battlerager barbarian's Reckless Abandon stack if I make multiple attacks on my turn?

Opposite of a diet

How can I get through very long and very dry, but also very useful technical documents when learning a new tool?

How to run a prison with the smallest amount of guards?

Is expanding the research of a group into machine learning as a PhD student risky?

What is the best translation for "slot" in the context of multiplayer video games?

mysql greatest n per group: latest message content and time for each conversation

Limit WHERE to MAX() & COUNT()mysql select latest per group using 2 different tables along with self joinGreatest N per Group in SQL ServerAlternative to removing ONLY_FULL_GROUP_BYIncompatible with sql_mode:only_full_group_byTroubles with: sql_mode=only_full_group_byProblem with only_full_group_byGetting the latest value using unique ID and user ID in MySQLGet a column from the latest row by another columnHow to optimize UPDATE with a nested SELECT subquery?

0

I have two tables, one for topics, and one for messages.
In terms of relationship, messages are posted within a topic, so each topic has many messages.

I am trying to make a query that would:
- list all the topics
- get the count of messages for each topic
- get the latest message posted for each topic (time and content)

I basically tried to do a query similar to:

SELECT

  topic.*,

  COUNT(msg.id),

  MAX(msg.time),

  msg.content



FROM topics



LEFT JOIN (

  SELECT

    msg.id,

    msg.time,

    msg.content,

    msg.topic_id

  FROM messages AS msg

  ORDER BY time DESC

) AS msg

ON msg.topic_id=topic.id



GROUP BY topic.id

However, this results in an error Expression #... (closed) of SELECT list is not in GROUP BY clause and contains nonaggregated column 'msg.time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I understand that I could just toggle the only_full_group_by setting, but my understanding is that I then have no guarantee that the msg.content will return me the latest message as expected.

I found many greatest n per group questions online, but most seem to only deal with a single table with the same table being queried in the query and subquery.

Is there any reliable way to achieve this with MySQL?

mysql greatest-n-per-group

share

asked 1 min ago

Simon NinonSimon Ninon

101

New contributor

Simon Ninon is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I have two tables, one for topics, and one for messages.
In terms of relationship, messages are posted within a topic, so each topic has many messages.

I am trying to make a query that would:
- list all the topics
- get the count of messages for each topic
- get the latest message posted for each topic (time and content)

I basically tried to do a query similar to:

SELECT

  topic.*,

  COUNT(msg.id),

  MAX(msg.time),

  msg.content



FROM topics



LEFT JOIN (

  SELECT

    msg.id,

    msg.time,

    msg.content,

    msg.topic_id

  FROM messages AS msg

  ORDER BY time DESC

) AS msg

ON msg.topic_id=topic.id



GROUP BY topic.id

However, this results in an error Expression #... (closed) of SELECT list is not in GROUP BY clause and contains nonaggregated column 'msg.time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I understand that I could just toggle the only_full_group_by setting, but my understanding is that I then have no guarantee that the msg.content will return me the latest message as expected.

I found many greatest n per group questions online, but most seem to only deal with a single table with the same table being queried in the query and subquery.

Is there any reliable way to achieve this with MySQL?

mysql greatest-n-per-group

share

asked 1 min ago

Simon NinonSimon Ninon

101

New contributor

Simon Ninon is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I have two tables, one for topics, and one for messages.
In terms of relationship, messages are posted within a topic, so each topic has many messages.

I am trying to make a query that would:
- list all the topics
- get the count of messages for each topic
- get the latest message posted for each topic (time and content)

I basically tried to do a query similar to:

SELECT

  topic.*,

  COUNT(msg.id),

  MAX(msg.time),

  msg.content



FROM topics



LEFT JOIN (

  SELECT

    msg.id,

    msg.time,

    msg.content,

    msg.topic_id

  FROM messages AS msg

  ORDER BY time DESC

) AS msg

ON msg.topic_id=topic.id



GROUP BY topic.id

However, this results in an error Expression #... (closed) of SELECT list is not in GROUP BY clause and contains nonaggregated column 'msg.time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I understand that I could just toggle the only_full_group_by setting, but my understanding is that I then have no guarantee that the msg.content will return me the latest message as expected.

I found many greatest n per group questions online, but most seem to only deal with a single table with the same table being queried in the query and subquery.

Is there any reliable way to achieve this with MySQL?

mysql greatest-n-per-group

share

asked 1 min ago

Simon NinonSimon Ninon

101

New contributor

Simon Ninon is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

SELECT

  topic.*,

  COUNT(msg.id),

  MAX(msg.time),

  msg.content



FROM topics



LEFT JOIN (

  SELECT

    msg.id,

    msg.time,

    msg.content,

    msg.topic_id

  FROM messages AS msg

  ORDER BY time DESC

) AS msg

ON msg.topic_id=topic.id



GROUP BY topic.id

share

asked 1 min ago

Simon NinonSimon Ninon

101

New contributor

Simon Ninon is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 1 min ago

Simon NinonSimon Ninon

101

New contributor

Simon Ninon is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 1 min ago

Simon NinonSimon Ninon

101

New contributor

Simon Ninon is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 1 min ago

Simon NinonSimon Ninon

101