CODEWITHSUNDEEP
mysqldatabasejoinaggregate-functions
AGK
AGK

Reputation: 297

How to combine aggregate with nonaggregated in mysql query

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

Upvotes: 0

Views: 237

Answers (3)

Ergest Basha
Ergest Basha

Reputation: 8973

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

Upvotes: 1

Booboo
Booboo

Reputation: 44108

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

Upvotes: 0

Akina
Akina

Reputation: 42622

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;

Upvotes: 0

Related Questions