How to find list of object that none of their version is bigger than a specific number?

Question

I am looking for a way to separate those object list that they don't have any number in "n_version1" more than 10. can you help me?

the output should be same as below:

I tried to sum n_version1 column in HAVING but it was not correct way. I am using Pl/SQL, oracle 9i.

Radim Bača · Answer 1 · 2017-09-18T09:50:23.027

2

Use NOT EXISTS

    SELECT *
    FROM table t1
    WHERE NOT EXISTS ( 
       SELECT *
       FROM table t2
       WHERE t1.v_object_name = t2.v_object_name AND t2.n_version > 10
    )

or NOT IN

    SELECT *
    FROM table t1
    WHERE t1.v_object_name NOT IN ( 
       SELECT t2.v_object_name
       FROM table t2
       WHERE t2.n_version > 10 AND t2.v_object_name IS NOT NULL
    )

edited Sep 18 '17 at 09:50

answered Sep 18 '17 at 09:47

Radim Bača

10,646
1
19
33

I have only one table. in that table (t1) you need to find list of the objects that none of the version is more than 10. table 2 is output illustration, I think you got me wrongly. I modify the question. – Amir Sep 18 '17 at 09:49
1

@Amir I did not, I have a one `table` as well, I just use aliases `t1` and `t2` for the same table – Radim Bača Sep 18 '17 at 09:52
I am checking give me a min – Amir Sep 18 '17 at 09:59
1

@Amir: Both queries are correct. check the [demo](http://rextester.com/PKDZN61744) – zarruq Sep 18 '17 at 09:59
I am not sure why I am getting ORA-00906: missing left parenthesis " FROM table t1" – Amir Sep 18 '17 at 10:01
is it in pl/sql? – Amir Sep 18 '17 at 10:03
1

@Amir: change `table` to anything else e.g. `t1`, `t2` etc as its a reserved keyword. Check the demo that i shared in above comment – zarruq Sep 18 '17 at 10:05
I got it. let me try again. – Amir Sep 18 '17 at 10:10
your query is correct but it is not efficient for my case. tq – Amir Sep 18 '17 at 10:37
1

@Amir Try to create an index `CREATE INDEX IX_nversion ON table(n_version) INCLUDE (v_object_name)` – Radim Bača Sep 18 '17 at 16:07
@RadimBača: well I have so many conditions in the actual query, if I want to follow your solution then I need to repeat all the conditions at least in four places which was not suitable. but if you check MT0 solution, it is just require to mention all my filter in single part. I hope you got my point. – Amir Sep 19 '17 at 07:10
1

@Amir ok, I just want you to realize that conditional aggregate solution (proposed by MT0) may be suboptimal in certain situations since it always scans the whole table and it can not use indexes, however, it may not be your case of course. – Radim Bača Sep 19 '17 at 07:15
@RadimBača: Thanks for your great explanation, I do agree with you & thanks for provided link as well, well in my case I think that solution was better. – Amir Sep 20 '17 at 04:10

score 2 · Accepted Answer · answered Sep 18 '17 at 10:26

2

You can use an analytic function to do it in a single table scan:

SELECT v_object_name,
       n_version1
FROM   (
  SELECT v_object_name,
         n_version1,
         COUNT( CASE WHEN n_version1 >= 10 THEN 1 END )
           OVER ( PARTITION BY v_object_name ) AS num_invalid
  FROM   your_table
)
WHERE  num_invalid = 0;

answered Sep 18 '17 at 10:26

MT0

143,790
11
59
117

This is the query I am looking for... Thank you so much, you used Oracle feature nicely. – Amir Sep 18 '17 at 10:35
brilliant solution! – Amir Sep 18 '17 at 10:52
As demonstrated here: https://stackoverflow.com/questions/45795898/conditional-aggregation-performance the efficiency of conditional aggregation vs. subqueries is highly dependent on the existence of indexes and selectivity of operators. This solution will ALWAYS lead to a full table scan which may be suboptimal in certain circumstances. – Radim Bača Sep 18 '17 at 13:07

score 1 · Answer 3 · answered Sep 18 '17 at 09:52

1

use:

SELECT *
FROM table t1
WHERE V_OBJECT_NAME NOT IN ( 
   SELECT V_OBJECT_NAME
   FROM table t2
   WHERE n_version1 >= 10
);

answered Sep 18 '17 at 09:52

Cyrille MODIANO

2,246
2
21
33

the query will return nothing if you have NULL value in `v_object_name` – Radim Bača Sep 18 '17 at 09:53
rigth, but it will not make any sense to have null values in the object_name – Cyrille MODIANO Sep 18 '17 at 09:55
your query is correct but it is not efficient for my case. tq – Amir Sep 18 '17 at 10:37

score 1 · Answer 4 · answered Sep 18 '17 at 10:07

Have a derived table that returns v_object_names having max version <= 10. JOIN with that result:

select t1.v_object_name, t1.n_version1
from tablename t1
join (select v_object_name
      from tablename
      group by v_object_name
      having max(n_version1) <= 10) t2
  on t1.v_object_name = t2.v_object_name

How to find list of object that none of their version is bigger than a specific number?

4 Answers4