Groupwise Max Multiple Columns

Question

Ive written a query that allows me to get the closest rooms using the groupwise max method. But how do I do groupwise max for multiple columns?

I have the query below where the subquery gives me the closest rooms and then joins them with the main table. Now I want to pick the first event that will happen next.

Ex: MIN(DATEDIFF( date, now())) > 0

SELECT name, date, t1.Room, descr, t1.D
FROM Events
JOIN
(
        SELECT Roomid, Room, Latitude, Longitude,
                ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) AS D
        FROM Rooms
        WHERE Latitude>(:minLat) AND Latitude<(:maxLat)
                AND Longitude>(:minLon) AND Longitude<(:maxLon)
                AND ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) < (:rad)
) AS t1 ON Events.roomid = t1.Roomid
GROUP BY Room
ORDER BY D

Can someone give me any pointers on how to do this?

Thanks!

P.S: The event should be the next occurring event. I don't use ABS(MIN()) because that will return events that already happened.

score 0 · Answer 1 · answered Mar 23 '12 at 00:32

0

Assuming D is your date, order by date difference and limit 1

SELECT name, date, t1.Room, descr, t1.D
FROM Events
JOIN
(
        SELECT Roomid, Room, Latitude, Longitude,
            ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) AS D
    FROM Rooms
    WHERE Latitude>(:minLat) AND Latitude<(:maxLat)
            AND Longitude>(:minLon) AND Longitude<(:maxLon)
            AND ACOS(SIN((:lat))*SIN(RADIANS(Latitude)) + COS((:lat))*COS(RADIANS(Latitude))*COS(RADIANS(Longitude)-(:lon)))*(:R) < (:rad)
) AS t1 ON Events.roomid = t1.Roomid
ORDER BY DATEDIFF( D, now())
LIMIT 1

answered Mar 23 '12 at 00:32

Matt Dodge

10,833
7
38
58

D is the distance, i want to get the next event that is going to happen and then order by D – maknelly Mar 23 '12 at 00:35
Oh my mistake, just use `date` instead of `D` in my example then. Is that not getting you what you want? – Matt Dodge Mar 23 '12 at 00:38
that will order it buy only dates i think. I also want it to order by D as well. Is that possible? Also, that would return dates that have already happened right? – maknelly Mar 23 '12 at 00:41
yes you can order by multiple columns, just comma separate them. However, I guess I don't get exactly what you are trying to do. If you want to get the next event why are you ordering by distance? – Matt Dodge Mar 23 '12 at 00:42

Groupwise Max Multiple Columns

1 Answers1