Query only numeric values of the earliest possible date

Question

I have to query from an oracle 11 db. With the query below I get all recent TAG_VALUE, TAG_DESC, INSERTION_DATE and PROJECT_ID from my database.

SELECT * 
FROM   (SELECT t.tag_value, 
               t.tag_desc, 
               u.update_as_of                    AS INSERTION_DATE, 
               p.proj_id                         AS PROJECT_ID, 
               Row_number() 
                 over( 
                   PARTITION BY p.proj_id 
                   ORDER BY u.update_as_of DESC) RN 
        FROM   project p 
               join update u 
                 ON p.project_id = u.project_id 
               join tag t 
                 ON t.tag_id = u.tag_id 
        WHERE  t.tag_desc LIKE 'Equity%') 
WHERE  rn = 1;

However, I came accross the cases that the answer of my request(without sorting it by date) can look like that:

+----------------------------------------------+
| TAG_VALUE TAG_DESC INSERTION_DATE PROJECT_ID |
+----------------------------------------------+
| null        Equity  14-DEC-14       1        |
| 0           Equity  14-DEC-14       1        |
| 312         Equity  14-DEC-14       1        |
| 23343       Equity  17-DEC-11       5        |
| 1263        Equity  16-DEC-11       5        |
| null        Equity  22-JÄN-14       2        |
| null        Equity  11-JÄN-14       2        |
| null        Equity  25-SEPT-13      2        |
| 0           Equity  20-SEPT-13      2        |
| 1234        Equity  19-SEPT-13      2        |
| 13415       Equity  18-SEPT-13      2        |
| 99999       Equity  16-OCT-10       9        |
+----------------------------------------------+

My Result Set should look like that:

+----------------------------------------------+
| TAG_VALUE TAG_DESC INSERTION_DATE PROJECT_ID |
+----------------------------------------------+
| 312         Equity  14-DEC-14       1        |
| 23343       Equity  17-DEC-11       5        |
| 1234        Equity  19-SEPT-13      2        |
| 99999       Equity  16-OCT-10       9        |
+----------------------------------------------+

There are two cases, which basically focus on the same problem:

• As you can see there are two cases, when the project_id = 1 the insertion date is always the same. However, with my query above I still get null back because of the ordering. How can I get the number 312 back without getting the null or the 0 value?
• If projectID = 2 there are different Insertion dates and the earlier dates have as TAG_VALUE null elements. However, I would like to have the tagValue of | 1234 Equity 19-SEPT-13 2 |, because it is the latest value?

How, can I basically ignore all null and also 0 values values and only take the numeric, which is greater than 0 value with the earliest date?

I really appreciate your answer!

Answer 1

Considering that your grouping happens within PROJECT_ID for INSERTION_DATE DESC and a positive TAG_VALUE, I tweaked the analytical functions to achieve the result. This may not be a robust solution, but will certainly help you.

Data Setup:

CREATE TABLE Table1
    ("TAG_VALUE" varchar2(5), "TAG_DESC" varchar2(6), "INSERTION_DATE" varchar2(10), "PROJECT_ID" int)
;

INSERT ALL 
    INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID")
         VALUES (NULL, 'Equity', '14-DEC-14', 1)
    INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID")
         VALUES ('0', 'Equity', '14-DEC-14', 1)
    INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID")
         VALUES ('312', 'Equity', '14-DEC-14', 1)
    INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID")
         VALUES ('23343', 'Equity', '17-DEC-11', 5)
    INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID")
         VALUES ('1263', 'Equity', '16-DEC-11', 5)
    INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID")
         VALUES (NULL, 'Equity', '22-JÄN-14', 2)
    INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID")
         VALUES (NULL, 'Equity', '11-JÄN-14', 2)
    INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID")
         VALUES (NULL, 'Equity', '25-SEPT-13', 2)
    INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID")
         VALUES ('0', 'Equity', '20-SEPT-13', 2)
    INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID")
         VALUES ('1234', 'Equity', '19-SEPT-13', 2)
    INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID")
         VALUES ('13415', 'Equity', '18-SEPT-13', 2)
    INTO Table1 ("TAG_VALUE", "TAG_DESC", "INSERTION_DATE", "PROJECT_ID")
         VALUES ('99999', 'Equity', '16-OCT-10', 9)
SELECT * FROM dual
;

Query:

SELECT tag_value, 
       tag_desc, 
       insertion_date, 
       project_id 
FROM   (SELECT tag_value, 
               tag_desc, 
               insertion_date, 
               project_id, 
               Last_value(Decode(tag_value, 0, NULL, 
                                            tag_value) ignore nulls) 
                 over ( 
                   PARTITION BY project_id 
                   ORDER BY insertion_date ROWS BETWEEN unbounded preceding AND 
                 unbounded 
                 following ) new_tag_value 
        FROM   table1) 
WHERE  tag_value = new_tag_value; 

Result:

TAG_VALUE   TAG_DESC    INSERTION_DATE  PROJECT_ID
312         Equity      14-DEC-14       1
1234        Equity      19-SEPT-13      2
23343       Equity      17-DEC-11       5
99999       Equity      16-OCT-10       9

Here is the fiddle

Answer 2

Your question is: "How, can I basically ignore all null and also 0 values values"

The simple answer is: By removing those records in the WHERE clause.

I use AND t.tag_value > 0 here. You can replace it with AND t.tag_value <> 0 AND t.tag_value IS NOT NULL, if you want to allow negative values.

SELECT * 
FROM 
(
  SELECT 
    t.tag_value,
    t.tag_desc, 
    u.update_as_of AS INSERTION_DATE, 
    p.proj_id AS PROJECT_ID, 
    ROW_NUMBER() OVER(PARTITION BY p.proj_id ORDER BY u.update_as_of DESC) RN
  FROM updated u 
  JOIN project p ON p.project_id = u.project_id  
  JOIN tag t ON t.tag_id = u.tag_id 
  WHERE t.tag_desc LIKE 'Equity%' AND t.tag_value > 0
)
WHERE RN = 1;

Answer 3

You can select the min(Insertion_Date) and the project ID in an inner query from the table and filter the it by Tag_Value <> Null. Then you in the outer query, inner join the table with this inner query on the project_id and insertion_date.