ORACLE SELECT DISTINCT VALUE ONLY IN SOME COLUMNS

Question

    +----+------+-------+---------+---------+
    | id | order| value | type    | account | 
    +----+------+-------+---------+---------+
    |  1 | 1    |     a |       2 |    1    |
    |  1 | 2    |     b |       1 |    1    |
    |  1 | 3    |     c |       4 |    1    |
    |  1 | 4    |     d |       2 |    1    |
    |  1 | 5    |     e |       1 |    1    |
    |  1 | 5    |     f |       6 |    1    |
    |  2 | 6    |     g |       1 |    1    |
    +----+------+-------+---------+---------+

I need get a select of all fields of this table but only getting 1 row for each combination of id+type (I don't care the value of the type). But I tried some approach without result.

At the moment that I make an DISTINCT I cant include rest of the fields to make it available in a subquery. If I add ROWNUM in the subquery all rows will be different making this not working.

Some ideas?

My better query at the moment is this:

SELECT ID, TYPE, VALUE, ACCOUNT
FROM MYTABLE
WHERE ROWID IN (SELECT DISTINCT MAX(ROWID)    
                FROM MYTABLE
                GROUP BY ID, TYPE);

You want one row per `id` and `type`. The `value` can be taken from any row. And the `account`? Do you want this for account 1 only? Or do you want one row per `id` and `type` and `account` actually? Or do you want to take an arbitrary `account` from the rows just like the `value`? — Thorsten Kettner, Nov 18 '21 at 16:14
the account value is always the same for each id (is a value related). Sorry for privacy i can share the real values and I created a fast simple example with generic name-values — JuniorGuy, Nov 18 '21 at 16:24
You don't care which row is selected, but does it need to be the same row each time the query is run *(deterministic)*? Do you need it to be non-deterministic and ***able*** to return a different row each time it is run? Etc, etc? — MatBailie, Nov 18 '21 at 16:31
Does this answer your question? [Select First Row of Every Group in sql](https://stackoverflow.com/questions/16529701/select-first-row-of-every-group-in-sql) — astentx, Nov 18 '21 at 18:32

score 2 · Accepted Answer · 2021-11-18T16:17:31.977

2

It seems you need to select one (random) row for each distinct combination of id and type. If so, you could do that efficiently using the row_number analytic function. Something like this:

select id, type, value, account
from   (
         select id, type, value, account, 
                row_number() over (partition by id, type order by null) as rn
         from   your_table
       )
where  rn = 1
;

order by null means random ordering of rows within each group (partition) by (id, type); this means that the ordering step, which is usually time-consuming, will be trivial in this case. Also, Oracle optimizes such queries (for the filter rn = 1).

Or, in versions 12.1 and higher, you can get the same with the match_recognize clause:

select id, type, value, account
from   my_table
match_recognize (
  partition by id, type
  all rows  per match
  pattern   (^r)
  define    r as null is null
);

This partitions the rows by id and type, it doesn't order them (which means random ordering), and selects just the "first" row from each partition. Note that some analytic functions, including row_number(), require an order by clause (even when we don't care about the ordering) - order by null is customary, but it can't be left out completely. By contrast, in match_recognize you can leave out the order by clause (the default is "random order"). On the other hand, you can't leave out the define clause, even if it imposes no conditions whatsoever. Why Oracle doesn't use a default for that clause too, only Oracle knows.

edited Nov 18 '21 at 16:17

answered Nov 18 '21 at 16:12

There's a seemingly unique column *(named `order`, just to collide with the key word)* which could then be used to make this deterministic, though still arbitrary. – MatBailie Nov 18 '21 at 16:21
1

@MatBailie - but the OP explicitly used ROWID in his attempt, not this ORDER column, which suggests that he did NOT want to attach any importance to ORDER. – Nov 18 '21 at 16:22
I believe that's projection. OP says `I don't care the value of the type`, beyond that everything else is supposition. The OP may want determinism, may not care about determinism, may want to avoid determinism; it's entirely ambiguous *(the same goes for use of `order`; absence of its use is not presence of a desire to not use it)*. Thus, I only raise this as an ***option***, not a definitive requirement. – MatBailie Nov 18 '21 at 16:25
I wanted to say that We need select one of the repeated registers, not mind if value of type is 1,2,3... only need one of these of each id+type combination. – JuniorGuy Nov 18 '21 at 16:27
In 19c they also introduced [`any_value`](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ANY_VALUE.html#GUID-A3C47D5E-B145-40B2-93D2-CA3BA65C2D81) aggregate function, that returns one expression value per group, that may be more readable (but random attribute of a group is quite strange requirement). It would be interesting to check the performance due to "It is optimized to return the first value" – astentx Nov 18 '21 at 18:38
@astentx - I wasn't aware of this new function. A quick test on livesql shows that the function does not necessarily return all values **from the same row**. It may pick a value from one row, another value from another row (still within the same group). Specifically, like other aggregate functions, it seems to ignore nulls; so if the "first" row has non-null values in some columns, those would be picked by `any_value`, but if other columns (in the same row) are null, then `any_value` for those columns will pick the first NON-NULL value (in each column) from other rows. – Nov 18 '21 at 19:55
Nice answer @mathguy, this works great and I learned a little over the row_number() over - partition. Thanks all for your help – JuniorGuy Nov 19 '21 at 08:23

ORACLE SELECT DISTINCT VALUE ONLY IN SOME COLUMNS

1 Answers1