[theRemix]

i need advice on a certain query. how would i re-write the query to return oldest record per id that is not used, OR if images of an id are used, return the most recent used record of that id?

for example, if my table contained.

SELECT * from TABLE;

+----+-----+-----+----+----+---------------------------------+-------------------------+---------------------+------+
| id | x   | y   | w  | h  | file			     | link			| created_at	     | used |
+----+-----+-----+----+----+---------------------------------+-------------------------+---------------------+------+
| 92 |  83 | 698 | 50 | 50 | 07-06-18-18-35-47_02711929.jpg  | http://something.com	| 2007-06-18 18:35:47 | 1  |
| 94 | 301 | 708 | 50 | 50 | 07-06-25-12-56-57_will.jpg	     | http://something.com	| 2007-06-25 12:56:56 | 0  |
| 96 | 537 | 644 | 50 | 50 | 07-06-25-13-36-07_izuna-cut.png | http://something.com	| 2007-06-25 13:36:05 | 1  |
| 97 |  83 | 698 | 50 | 50 | 07-06-18-13-35-47_something.jpg | http://something.com	| 2007-06-25 13:35:47 | 1  |
| 92 | 301 | 708 | 50 | 50 | 07-06-25-13-56-57_something.jpg | http://something.com	| 2007-06-25 13:56:56 | 1  |
| 92 | 537 | 644 | 50 | 50 | 07-06-25-13-36-07_something.png | http://something.com	| 2007-06-25 13:36:05 | 0  |
| 94 |  83 | 698 | 50 | 50 | 07-06-18-13-35-47_something.jpg | http://something.com	| 2007-06-25 13:35:47 | 1  |
| 94 | 301 | 708 | 50 | 50 | 07-06-25-13-56-57_something.jpg | http://something.com	| 2007-06-25 13:56:56 | 0  |
| 96 | 537 | 644 | 50 | 50 | 07-06-25-13-36-07_something.png | http://something.com	| 2007-06-25 13:38:05 | 1  |
+----+-----+-----+----+----+---------------------------------+-------------------------+---------------------+------+

so if all rows that share a particular id, have at least one used = 0 row, then return the one row that is used = 0, and the oldest.
AND if all rows that share a particular id, have all used = 1, then return the one newest row of that group.

the result should be this:

+----+-----+-----+----+----+---------------------------------+-------------------------+---------------------+------+
| id | x   | y   | w  | h  | file			     | link				| created_at	     | used |
+----+-----+-----+----+----+---------------------------------+-------------------------+---------------------+------+
| 94 | 301 | 708 | 50 | 50 | 07-06-25-12-56-57_will.jpg      | http://something.com	| 2007-06-25 12:56:56 | 0  |
| 97 |  83 | 698 | 50 | 50 | 07-06-18-13-35-47_something.jpg | http://something.com	| 2007-06-25 13:35:47 | 1  |
| 92 | 537 | 644 | 50 | 50 | 07-06-25-13-36-07_something.png | http://something.com	| 2007-06-25 13:36:05 | 0  |
| 96 | 537 | 644 | 50 | 50 | 07-06-25-13-36-07_something.png | http://something.com	| 2007-06-25 13:38:05 | 1  |
+----+-----+-----+----+----+---------------------------------+-------------------------+---------------------+------+

this is as far as i got, but it doesn't return the rows i want. it's not quite there yet.

(SELECT DISTINCT * FROM TABLES WHERE used = 0 ORDER BY created_at ASC LIMIT 1)
UNION
(SELECT DISTINCT * FROM TABLES WHERE used = 1 ORDER BY created_at DESC LIMIT 1);

any help would be greatly appreciated.