How to combine aggregate with nonaggregated in mysql query

Question

I have two tables Locations and competitors, i want to get the latest row from locations for each competitor order by timestamp in mysql. I don't know how to combine aggregate with non aggregate without changing the sql mode 'ONLY_FULL_GROUP_BY'

Tabels looks like

 Location
| lng       | lat     | competitor_id | timestamp |
| 99.99999  | 88.99999|     1666      |2021-07-29 10:40
| 65.99999  | 36.99999|     1555      |2021-07-29 10:12
| 35.99999  | 42.99999|     1888      |2021-07-29 10:28
| 28.99999  | 58.99999|     1666      |2021-07-29 10:17
| 47.99999  | 32.99999|     1555      |2021-07-29 10:42
| 22.99999  | 15.99999|     1888      |2021-07-29 10:05
 
Competitors
| Name          | team    | competitor_id
| Artial Dedino | Scuderia |     1666      
| Naruto Belica | Redb|          1555      
| Maranelino Kita | Sport|       1888

I try

SELECT c.`name`,l.lat,l.lng,l.timestamp
FROM competitors as c
INNER JOIN locations as l ON l.competitor_id = c.number
GROUP BY c.number
ORDER BY  l.timestamp DESC

Need result like

 | Naruto Belica | Redb| 2021-07-29 10:42    
 | Artial Dedino | Scuderia | 2021-07-29 10:40  
 | Maranelino Kita | Sport| 2021-07-29 10:28

Your so-called result shows three columns yet your select statement is selecting many more columns. You show your timestamp column as having name `timestamp` yet in your SQL you call it `inserted_at`. Read carefully [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query). — Booboo, Jul 29 '21 at 12:32
@Booboo timestamp is inserted_at, i fix it thank you but this is not the reason for not working — AGK, Jul 29 '21 at 12:40
Your column names in you select statement still do not have any resemblance to what you claim is your result. `c.number`? What is that? Do your really want as output the `lat` and `lng` columns? Don't you want `name`, `team` and `timestamp`. **We know why it is not working. The point is if we don't know what you want, we still can't give you an answer. Could anything be more clearer than that?** — Booboo, Jul 29 '21 at 12:42
@Booboo c is alias of competitors, these tables has lot of columns, i want only some of these, problem isn't select column but aggregated columns, please focus on question not on select statement! — AGK, Jul 29 '21 at 12:49
If you knew more than you knew, you would know how off base a comment you just made. If you only wanted the `timestamp` column from the `locations` table, there would be a simple select statement for doing that. If, however, you needed multiple columns from that table, another method would be called for. That's why when there is a discrepancy between the columns you show in your select statement and the columns you claim you want in the result, there is a lack of clarity that cannot be ignored. — Booboo, Jul 29 '21 at 13:51

Ergest Basha · Accepted Answer · 2021-07-29T13:47:48.450

1

When MySQL only_full_group_by mode is on, it means this means that if you GROUP BY by some column, then you can only select one of two things , the column you group by and an aggregate function like MAX(), MIN()...; If you do not want to change your sql_mode (that may result in problems in the future ), below query should work for you.

    SELECT max(name) as name,
           max(team) as team,
           max(timestamp)  as timestamp
FROM competitors join location using(competitor_id)
where competitor_id in ( 
select distinct competitor_id from  competitors ) 
group by competitor_id
order by timestamp DESC ;

http://www.sqlfiddle.com/#!9/2cc8a6/1

Refrence link: SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

edited Jul 29 '21 at 13:47

answered Jul 29 '21 at 12:29

Ergest Basha

7,870
4
8
28

Note that this appears to be the most frequently asked question under mysql tag. Innumerable 'correct' answers have already been provided. – Jul 29 '21 at 12:32
inserted_at is timestamp I fixed it, this query doesn't return any value @basha04 – AGK Jul 29 '21 at 12:38
@basha04 yes sir, – AGK Jul 29 '21 at 12:41

Akina · Answer 2 · 2021-07-29T13:22:22.800

0

Need result like

| Naruto Belica | Redb| 2021-07-29 10:42    
| Artial Dedino | Scuderia | 2021-07-29 10:40  
| Maranelino Kita | Sport| 2021-07-29 10:28

SELECT c.name, c.team, MAX(l.`timestamp`) timestamp
FROM Competitors c
JOIN Location l USING (competitor_id)
GROUP BY c.name, c.team;

edited Jul 29 '21 at 13:22

answered Jul 29 '21 at 13:13

Akina

39,301
5
14
25

You can't use aggregate columns with non aggregated columns in the select list – AGK Jul 29 '21 at 13:18
@Sterko Last line was lost... fixed. – Akina Jul 29 '21 at 13:22
@basha04 OP can do this by himself if needed, I think. – Akina Jul 29 '21 at 13:50

score 0 · Answer 3 · answered Jul 29 '21 at 13:39

You could try the following.

select c.name, l.lat, l.lng, l.timestamp
from competitors c
join (
    select l.* from locations where timestamp = (
        select l2.timestamp from locations where l2.competitor_id = l.competitor_id
        order by l2.timestamp desc
        limit 1
    )
) l on c.competitor_id = l.competitor_id

How to combine aggregate with nonaggregated in mysql query

3 Answers3