MySQL Forums
Forum List  »  Perl

Some Complex SQL Query
Posted by: Aaron Nicoli
Date: September 15, 2010 09:36PM

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,

Edited 1 time(s). Last edit at 09/15/2010 09:36PM by Aaron Nicoli.

Options: ReplyQuote

Written By
Some Complex SQL Query
September 15, 2010 09:36PM
September 17, 2010 06:48PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.