Oracle SQL - Select distinct values from a given list

sql select unique values from multiple columns
sql select distinct multiple columns
select distinct on one column with multiple columns returned
how to select unique records in oracle without using distinct
mysql select distinct on one column
oracle select distinct group by
sql select distinct values and count of each
oracle distinct vs group by

I have searched extensively to try find a way of selecting distinct values from a given list that don't exist in a table, but there doesn't seem to be a simple way of doing this.

I was wondering if this is possible to do somehow, or is the easiest solution to put the values into a table and select distinct from there?

This is the gist of what I am trying to do:

Select distinct column_name from dual where column_name in ('one','two','two','elephant');

The alternative for "dual joins" is the built in varchar2 array. Here is the example

select distinct column_value
from   table(
         sys.odcivarchar2list(
           'one','two','two','elephant'
         )
       )

Oracle SELECT DISTINCT By Practical Examples, The DISTINCT clause is used in a SELECT statement to filter duplicate rows in It ensures that rows returned are unique for the column or columns specified in the SELECT you just need to specify the column list in the SELECT clause as follows: Note that DISTINCT is synonym of UNIQUE which is not SQL standard. To retrieve unique data based on multiple columns, you just need to specify the column list in the SELECT clause as follows: SELECT DISTINCT column_1, column_2, column_3 FROM table_name; In this syntax, the combination of values in the column_1, column_2, and column_3 are used to determine the uniqueness of the data. The DISTINCT clause can be used only in the SELECT statement. Note that DISTINCT is synonym of UNIQUE which is not SQL standard. It is a good practice to always use DISTINCT

The constructs with in won't work, unless you have a query that returns all values you specify and maybe a bit more, so if you have a set of letters, you could input it in a query that returns all letters. That would give you something like this:

select
  *
from (
  select chr(96 + level) as letter
  from dual
  connect by level <= 26)
where
  letter in ('a', 'a', 'b');

Of course, that would work for fixed limited sets, like the whole alphabet, or even numbers from 1 to 1000, but it's not a way to convert any set to a distinct list.

Alternative: dual joins

You can select a single value using the fake table dual. You can even make a union of such selects, although it looks a bit cumbersome:

select 'a' as MyValue from dual
union select 'a' from dual
union select 'b' from dual

If you use union instead of union all, the query will implicitly return distinct values only.

Alternative: Split string

If you have the values in a string, you could split that string and return the items. There are various ways to do that, but not out of the box. You could have a look at the question Splitting string into multiple rows to which the answers show various solutions.

SQL SELECT DISTINCT, COUNT, ROWS, Problem: List all unique supplier countries in alphabetical order. SELECT DISTINCT Country; FROM Supplier; ORDER BY COUNTRY. Result: 16  This removes the need to create complex query processing to find the distinct values prior to using the aggregate LISTAGG function. With the DISTINCT option, the processing to remove duplicate values can be done directly within the LISTAGG function. The result is simpler, faster, more efficient SQL. Area Oracle 19c; Contributor Oracle

Try something like this:

select distinct trim(regexp_substr('a,b,b,c','[^,]+', 1, level) ) value
from dual
connect by regexp_substr('a,b,b,c', '[^,]+', 1, level) is not null

SQL SELECT DISTINCT Statement, Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values. SELECT DISTINCT Syntax. The DISTINCT SQL operator is used to remove duplicates from the resulting SELECT operator set. Syntax for DISTINCT statement in SQL. SELECT DISTINCT exprs FROM tabs [WHERE conds]; where: exprs – Columns or calculations that you want to get. tabs – The tables from which you want the records. The FROM sentence must contain at least one table.

Ask TOM "Distinct Values from Multidimensional Collection", Is there a way to retrieve Unique records from a multidimensional collection? Tried the below, but it's not giving the desired results. SQL based is fine, but the SQL query, "select v_nt_tbl_rec multiset union distinct v_nt_tbl_rec into Then we want to sub-select from that to get a distinct list of descriptions. sql,sql-server,phpmyadmin. You can use a SELECT statement when inserting into a table. What I would do here is write a select statement that pulls all of the columns you need first. You will have to do a full outer join (simulated by a union of left and right joins) because some

Script: 19C LISTAGG DISTINCT, With the new DISTINCT keyword, duplicate values can be removed from the specified expression before concatenation into a single string. select d.dname, listagg (e.job,', ' on overflow truncate with count) within group  COUNT () function with distinct clause SQL COUNT () function with DISTINCT clause eliminates the repetitive appearance of the same data. The DISTINCT can come only once in a given select statement.

Ask TOM "LISTAGG not giving distinct values", Below query gives distinct values in col2 which I'm good with: SQL> SQL> select listagg(v,',') within group ( order by v ) 2 from t 3 where x = 1;  The SQL SELECT DISTINCT Statement The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

Comments
  • Thank you for you answer, but I've edited the question to better reflect what I am trying to do. The dataset will be large and made up of many varchar words rather than individual letters, I think the methods you have shown above might be more effort than it would be worth but thank you for showing that method!
  • Jup, i'd look at the alternative solutions or the other question then.