How to select an element just once in plain SQL

I’ve seen other similar questions to mine, but I haven’t found one that could actually help me. I have a table, defined like so:

CREATE TABLE "DOWNLOAD_HISTORY" 
   (    "ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 344 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "IDUSER" NUMBER NOT NULL ENABLE, 
    "IDFORNITORE" NUMBER NOT NULL ENABLE, 
    "IDRFX" NUMBER NOT NULL ENABLE, 
    "DOCNUM" NUMBER NOT NULL ENABLE, 
    "DOWNLOAD_TIMESTAMP" TIMESTAMP (6) NOT NULL ENABLE
   ) ;

This DDL comes from an OracleDB12 database.
Following, some sample data

What I’d like to accomplish: if there’s already a row with the same tuple (IDRFX, DOCNUM), not including it in the SELECT statement. So, the tuple (384,1) should appear only once.

What I’ve done (so far): with the idea that this query could be performed even from a MSSQL database, I’ve tried to write these two queries in plain SQL.

SELECT * 
FROM DOWNLOAD_HISTORY dh 
WHERE dh.IDRFX = 384 AND ID IN (SELECT ID FROM DOWNLOAD_HISTORY dh2 WHERE dh.DOCNUM <> dh2.DOCNUM AND dh.IDRFX <> dh2.IDRFX )



SELECT t1.*
FROM DOWNLOAD_HISTORY t1
WHERE NOT EXISTS (SELECT * from DOWNLOAD_HISTORY t2
                  where t2.DOCNUM = t1.DOCNUM 
                    and t2.IDRFX = t1.IDRFX)

But none of them was actually able to get me anywhere. If possible, I’d like to ask where am I doing the mistake and how can I fix it.

Answer

You can use row_number analytical function as follows:

SELECT * FROM
(SELECT t1.*, row_number() over (partition by t1.DOCNUM, t1.IDRFX order by 1) as rn
FROM DOWNLOAD_HISTORY t1)
WHERE RN = 1;

OR you can use the NOT EXISTS query as follows:

SELECT t1.*
FROM DOWNLOAD_HISTORY t1
WHERE NOT EXISTS (SELECT * from DOWNLOAD_HISTORY t2
                  where t2.DOCNUM = t1.DOCNUM 
                    and t2.IDRFX = t1.IDRFX
                    and t2.ID > t1.ID); -- add only this condition in your query

Advertisement

Answer