Oracle Equivalent of Postgres' Distinct On

Oracle equivalent of Postgres' DISTINCT ON?

The same effect can be replicated in Oracle either by using the first_value() function or by using one of the rank() or row_number() functions.

Both variants also work in Postgres.

first_value()

select distinct col1, 
first_value(col2) over (partition by col1 order by col2 asc)
from tmp

first_value gives the first value for the partition, but repeats it for each row, so it is necessary to use it in combination with distinct to get a single row for each partition.

row_number() / rank()

select col1, col2 from (
  select col1, col2, 
  row_number() over (partition by col1 order by col2 asc) as rownumber 
  from tmp
) foo
where rownumber = 1

Replacing row_number() with rank() in this example yields the same result.

A feature of this variant is that it can be used to fetch the first N rows for a given partition (e.g. "last 3 updated") simply by changing rownumber = 1 to rownumber <= N.

SELECT DISTINCT OVER TEXT as needs over common query in PostgreSQL and Oracle

Oracle doesn't support DISTINCT with CLOB column. You can use TO_CHAR (when the maximum length of CLOB column is less than 4000)

SELECT DISTINCT to_char(description) from FOLDER;

Since you mentioned in comment that it is working fine for oracle, I assume there is no length issues. As you are looking for common solution for both POSTGRES and ORACLE, I would propose CAST

SELECT DISTINCT CAST(description as varchar(3000)) from FOLDER;

This should work in both databases.Change the VARCHAR(N) accordingly upto the maximum limit.

CAUTION : I won't recommend using VARCHAR instead of VARCHAR2 in oracle.

The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings.

How do I (or can I) SELECT DISTINCT on multiple columns?

SELECT DISTINCT a,b,c FROM t

is roughly equivalent to:

SELECT a,b,c FROM t GROUP BY a,b,c

It's a good idea to get used to the GROUP BY syntax, as it's more powerful.

For your query, I'd do it like this:

UPDATE sales
SET status='ACTIVE'
WHERE id IN
(
    SELECT id
    FROM sales S
    INNER JOIN
    (
        SELECT saleprice, saledate
        FROM sales
        GROUP BY saleprice, saledate
        HAVING COUNT(*) = 1 
    ) T
    ON S.saleprice=T.saleprice AND s.saledate=T.saledate
 )

using select distinct with multiple columns but all columns musnt be distinct

Postgresql supports the DISTINCT ON syntax: http://www.postgresql.org/docs/current/interactive/sql-select.html

select distinct on (a.personalId, a.fileId) a.personalId, a.fileId, a.name, a.surname, a.address
from my_table a

See Oracle equivalent of Postgres' DISTINCT ON? for how to do this in Oracle.

How to use multiple distinct on statements in Postgres while retaining the correct order

I'd add dump_date DESC to the ORDER

SELECT DISTINCT ON (time, forecast)
    *
FROM t
ORDER BY time, forecast, dump_date DESC

How to implement Oracle count(distinct) over partition in Postgres

Postgres doesn't support count(distinct) directly. But you can implement it with a subquery:

select . . .,
       sum( (seqnum_tm = 1)::int) as mob_segments_count ,
       sum( (seqnum_tr = 1)::int) as countries_count
from (select . . .,
             row_number() over (partition by pm.trans_id, pm.country_reg_region_cd order by pm.country_reg_region_cd) as seqnum_tr,
             row_number() over (partition by pm.trans_id, pm.mobseg_state order by pm.pm.mobseg_state) as seqnum_tm
      . . .
     ) . . .

The idea is simple. Calculate row_number() on the partition by keys and the distinct column. Then, just add up the number of times when the value is "1". This requires a subquery, because you cannot nest window functions.

SELECT DISTINCT, ORDER BY expressions must appear in select list - error when migrating from Oracle to Postgres

Your query works in Postgres 13 and later. If you start a new project, then you should use the most recent version, rather than starting with a slightly outdated version

In older versions, you can wrap it in a derived table to get around that.

select *
from (
  SELECT DISTINCT * 
  FROM TABLE_A A 
    JOIN TABLE_B B ON A.BIC = B.BIC 
  WHERE B.CUST_NUMBER=?
    AND A.LOCATION=?
    AND B.STATUS=?
    AND B.LIVE=TRUE
) a
ORDER BY A.TYPE, A.PARTY

Postgres 12 example

Postgres 14 example

Postgres: Why DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s)?

Well, the ORDER BY is needed to keep those rows together that share the same value for the "distinct columns". The database processes them sequentially discarding all subsequent rows from the same set. If the rows weren't sorted, this wouldn't be easily possible.

Assume this set of rows:

c1 | c2 
---+----
 1 | 100
 2 |  10
 1 | 200
 2 |  15

If you want the c1 to be unique and pick the highest c2 you would need to use

select distinct on (c1) *
from the_table
order by c1, c2 desc;

The order by itself will generate the following result:

c1 | c2 
---+----
 1 | 200
 1 | 100
 2 |  15
 2 |  10

By processing that result row-by-row the database can now efficiently discard every but the first row for each c1 value by simply checking if that value changes from row to another. If the result wasn't sorted this check would be become far more complicated.

---

Related Topics