CODEWITHSUNDEEP
mysqlmaxmin
Rikard
Rikard

Reputation: 83

Fetch MIN and MAX in the same row

Imagine a table called training_route which contains several rows each with a rowId, gpsLocation and a athleteId and consider the following:

Ex.

rowId   athleteId   gpsLocation
100       1           "40.7127837,-74.00594130000002" <- first location for athlete #1
101       1           "41.1234872,-71.41300000022342"
102       1           "42.1234872,-69.23112200022342" <- last location for athlete #1
103       2           "39.5993499,-74.00594130000002" <- first location for athlete #2
104       2           "38.9093885,-73.31300000022342"
106       2           "37.1234872,-63.34215200022342" <- last location for athlete #2
107       3           ...

What I would like to have is the first and last location for each route grouped by the athleteId, in the very same row by the very same query:

athleteId   firstLocation                       lastLocation
1           "40.7127837,-74.00594130000002"     "42.1234872,-69.23112200022342"
2           "39.5993499,-74.00594130000002"     "37.1234872,-63.34215200022342"
3           ...             ...

How would that MySQL-query look like?

P.S.

I've tried something like this:

SELECT training_route.athleteId,
       ( SELECT training_route.gpsLocation FROM training_route WHERE training_route.athleteId = route.athleteId ORDER BY training_route.rowId ASC  LIMIT 1 ) AS firstLocation,
       ( SELECT training_route.gpsLocation FROM training_route WHERE training_route.athleteId = route.athleteId ORDER BY training_route.rowId DESC LIMIT 1 ) AS lastLocation,
FROM   training_route AS route
WHERE  training_route.athleteId IN ( 1, 2, 3 ) GROUP BY training_route.athleteId;

But I'm ashamed that's the best I've come up with so far since it's totally unacceptable perfomance-wise.

Upvotes: 1

Views: 90

Answers (1)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

Following should work with mysql

SELECT *
FROM   (
        SELECT MIN(rowid) minrowid
               , MAX(rowid) maxrowid
               , athleteid
        FROM  training_route
        GROUP BY
              athleteid
       ) minmax
       INNER JOIN training_route trmin ON trmin.athleteid = minmax.athleteid 
                                          AND trmin.minrowid = minmax.rowid
       INNER JOIN training_route trmax ON trmax.athleteid = minmax.athleteid 
                                          AND trmax.maxrowid = minmax.rowid

The idea is to

  • get the min and max rowid's for each athlete
  • join the previous results again with the table to get the gps coörodinates for the min rowid
  • and join again with the original table to get the gps coörodinates for the max rowid

Upvotes: 3

Related Questions