Some Complex SQL Query
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.
Edited 1 time(s). Last edit at 09/15/2010 09:36PM by Aaron Nicoli.