Problem:

22:17 < Confessor> 'SELECT DISTINCT
`Main`.`Location`,`Locations`.`Name`,`Locations`.`City`,`Locations`.`State`
FROM `Main` INNER JOIN `Locations` ON `Main`.`Location` = 
                   `Locations`.`ID` WHERE `Main`.`Location` IS NOT NULL ORDER
BY `Main`.`Date` DESC'
22:17 < Confessor> I'm trying to use this query to return a list of the most
recently used locations from which I've posted my Journal, *excluding*
duplicates.
22:18 < Confessor> Unfortunately, the DISTINCT appears to be removing the
duplicates before the ordering happens.
22:18 -!- Wildblue [~Wildblue@c-98-233-92-105.hsd1.md.comcast.net] has joined
#php
22:19 < Confessor> Which means that locations are listed at their *first* use,
rather than their most recent.
22:19 < Confessor> How would I modify my query to fix this?
22:21 <@pizza_> it's not so much that as you can't control which record
DISTINCT picks; it tends to be the first one i believe; not sure if there are
any guarentees about 
                that behavior
22:22 < Confessor> Damn; I was hoping I might be able to use some sort of
subquery structure to ensure that the DISTINCT operation occurs on a
preordered table.
22:22 <@pizza_> i'm sure it's possible
22:23 < Confessor> I tried 'SELECT DISTINCT FROM ([SELECT STATEMENT])'
22:24 < Confessor> But that was just me mucking around after my searches found
nothing applicable.
22:24 < Confessor> And it didn't return a result set.




Exploration:


pizza=> CREATE TABLE latest (id CHAR, when_ TIME);
CREATE TABLE
pizza=> INSERT INTO latest VALUES ('A', NOW());
INSERT 25341 1
pizza=> INSERT INTO latest VALUES ('A', NOW());
INSERT 25342 1
pizza=> INSERT INTO latest VALUES ('B', NOW());
INSERT 25343 1
pizza=> SELECT * from latest;
 id |      when_
----+-----------------
 A  | 22:25:23.960251
 A  | 22:25:33.511183
 B  | 22:25:41.254337
(3 rows)

pizza=> SELECT DISTINCT latest.id, foo.maxwhen FROM latest
pizza-> JOIN (SELECT id, MAX(when_) AS maxwhen FROM latest GROUP BY id) AS foo
pizza-> ON latest.id = foo.id;
 id |    maxwhen
----+-----------------
 A  | 22:25:33.511183
 B  | 22:25:41.254337
(2 rows)

pizza=>