Hi all,

I have a bit of an issue, I am working on a bit of a CMDB for a friend, it's to do with real estate.

Anyway, this is my situation.

I have a table which contains all the properties (forsale, sold, etc) in the DB named "property".

Now, this is what I want to achieve, I wish to create a output table ordered by the age of the property in our database. Each row has these important fields "id" (INT auto row id) "pid" (property id) "saved" (datetime of data) "active" (INT 0 or 1) and "name" (VARCHAR).

Now each property (pid) will have multiple rows a new created and date stamped (saved) for every update made to the property in the database.

So, the tech, I need to select only unique properties (pid's) and get their newest and oldest save times (saved), then output:
Their age (oldest "saved" compared to curtime())
Name of property ("name", but coming from the newest save "saved")

AND... only pids with the active flag = 1, should be outputted and I want the output sorted by newest to oldest.

THEN.. if this really was not enough, can I please have only the details for the 25 newest pids (for page one), then in my next query (26-50 newest) so on and so fourth.

Sorry guys, but I really just can't get my head around where to start, so thought I would present to you what I am overall trying to achieve and see if you can help piece things together.

Thanks, big thanks, in advance,
Aaron.

---------- Post updated at 02:11 PM ---------- Previous update was at 12:19 PM ----------

Here is some sample data:

Code:

+--+------+---------+---------+------------------------------+
| id | pid | name  | active |                  saved                             |
+--+------+---------+---------+------------------------------+
| 1  |   1     |    foo      |    1          | 2010-09-01 13:54:30 |
| 2  |   1     |    foz       |    1          | 2010-09-06 14:51:10 |
| 3  |   1     |    foe      |    1          | 2010-09-13 20:01:14 |
| 4  |   2    |    bar       |    1          | 2010-07-04 10:12:55 |
| 5  |   2     |    bar       |    1          | 2010-09-01 14:31:34 |
| 6  |   3     |    zzz     |    0          | 2010-09-16 11:11:27 |
+---+-----+---------+---------+--------------------------------+

OUTPUT SHOULD BE:

Code:

ID       PID       NAME          DAYS-OLD
3        1          foe               15
5         2         bar              74

---------- Post updated at 02:37 PM ---------- Previous update was at 02:11 PM ----------

This is my latest creation....

Code:

SELECT DISTINCT pid, ****name,**** (SELECT max(saved) WHERE pid = 2.pid) FROM property WHERE active = 1 ORDER BY DESC ****NEWEST OF THE FIRST RECORDS****;

where:
saved is my datestamped field.
name is my records data.
pid is the distinct id.


Problem that I see is this, if I go ORDER BY DESC saved;

then, this will just print them in order of the records "newest" saved datestamp (where I want the name from) but not the records "oldest" saved timestamp.

Code:

SELECT DISTINCT pid, *****name,***** (SELECT max(saved) WHERE pid = 2.pid) FROM property WHERE active = 1 ORDER BY DESC (SELECT min(saved) WHERE pid = 2.pid);

Is this possible...?
and on top of this I still can't see I extract my other data like name...