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:
OUTPUT SHOULD BE:
---------- Post updated at 02:37 PM ---------- Previous update was at 02:11 PM ----------
This is my latest creation....
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.
Is this possible...?
and on top of this I still can't see I extract my other data like name...
Hello,
I have three tables.
I need an SQL query (preferably Sybase) that will return all of the stringID values of table B where the following conditions exist:
(1) B.intID = A.intID
(2) B.intID != C.intID
or (B.intID = C.intID and
(C.v1 = 0 or C.v2... (2 Replies)
Hi there,
I have a hierarchical database that include 4 tables. Table A is the parent of B, B is Parent of C, C is parent of D. If I want to query everything in D that is associated with A.name, how do I do that? Thanks!
YanYan (0 Replies)
hello all
i have 2 columns every column in the following format
column1
2011-04-01 11:39:54
column2
2019-02-03 00:00:00
i want get difference between above data as following
2 days 11:39
how to do so ?
i tried many functions but nothing works
please advice what is the query... (6 Replies)
A file whose location i am not aware of contains the below text
<url>jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP)
(HOST=ngm2sn1p2-vip.mybank.net)(PORT=4001))(ADDRESS=(PROTOCOL=TCP)(HOST=ngm2sn2p2-vip.mybank.net)... (1 Reply)
Hello
i have created mysql query to compare to values and get difference in percentage
as following:
SELECT file_name, 100 - ((100 * (SELECT file_count FROM xipi_files z WHERE x.file_group = z.file_group AND x.file_name = z.file_name AND z.insert_date = CURDATE( ) - INTERVAL 1 DAY)) /... (1 Reply)
Hi,
I wish to find the latest occurance of the below string in my log file.
Once found, I need to search the below string after the above string
and display this
Request 331489 has passed
or
Request 331489 has failed
I would like my query to be platform... (11 Replies)
Hi,
I provide the path to a folder.
I would like so drill in each folder inside the folder i gave and show me the size in human readable form du -sh <folder-name> of the top 8 folders with maximum sizes.
For example:
Path of the folder: "/opt/app/myapp" has these directories.
ls... (2 Replies)
Hi All,
i have a table in mysql with the following data
Table name Test
Assettype Serial_No Status location
Mouse 123456 In Stock chennai
Mouse 98765 Allocated chennai
Keyboard ... (2 Replies)