CODEWITHSUNDEEP
sql
spujaname
spujaname

Reputation: 33

Remove row base on the date in query

I have a query which return me below result:

enter image description here

Basically I am looking for the result where its compare Document_Type if it's same then consider recent Scan_Date and eliminate the old rows for same document_type in example document_type PRT.

Please find my query

SELECT DISTINCT 
    ISNULL(DOCUMENT_TYPE_DESCRIPTION, '') DOCUMENT_TYPE, 
    CORP_DOCUMENT_GUID, SCAN_DATE,
    ISNULL(DOCUMENT_TITLE, '') DOCUMENT_NAME,
    ISNULL(OTHER_INDEX, '') OTHER_REFERENCE,
    ISNULL(DESCRIPTIONWS, '') DESCRIPTION,
    ISNULL(AUTHOR, 'SYSTEM') AUTHOR       
FROM 
    REFERENCE_VIEW ADRV
JOIN
    REFERENCE DR ON ADRV.corp_document_id = DR.CORP_DOCUMENT_ID
WHERE 
    DR.REFERENCE_NBR IN ('XXXXXX')

UNION 

SELECT DISTINCT 
    ISNULL(DOCUMENT_TYPE_DESCRIPTION, '') DOCUMENT_TYPE,
    CORP_DOCUMENT_GUID, SCAN_DATE,
    ISNULL(DOCUMENT_TITLE, '') DOCUMENT_NAME,
    ISNULL(OTHER_INDEX, '') OTHER_REFERENCE,
    ISNULL(DESCRIPTIONWS, '') DESCRIPTION,
    ISNULL(AUTHOR, 'SYSTEM') AUTHOR
FROM 
    REFERENCE_VIEW ADRV
WHERE
    ADRV.SHIPMENT_ID IN (SELECT DISTINCT SHIPMENT_ID 
                         FROM REFERENCE_VIEW  WITH (NOLOCK) 
                         WHERE HOUSE_BILL = 'XXXXXX')

Expected result is as below enter image description here Where second document PRT removed as its old document ( 3/10/20) . Please advice how I will achieve this !!

Upvotes: 1

Views: 59

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Your code looks like SQL Server. I would suggest window functions. Something like this:

with q as (
      <your query here>
     )
select q.*
from (select q.*,
             row_number() over (partition by document_type order by scan_date desc) as seqnum
      from q
     ) q
where seqnum = 1;

Upvotes: 1

user9601310
user9601310

Reputation: 1086

I believe you could try this: (Fiddle here works for DB2, PostgreSQL, MySQL, SQLite)

WITH cte AS (
... your existing query here ...
)
SELECT * FROM cte
WHERE (document_type, scan_date) IN 
    (SELECT document_type, MAX(scan_date) FROM cte GROUP BY document_type)

This assumes scan_date is defined as a timestamp. Also I'm a little surprised you only consider the document type and not the document name in your filter.

Upvotes: 0

Syed Mushtaq
Syed Mushtaq

Reputation: 321

This will work too.

  select * from table_name
  where (document_type, scan_date) in (select document_type, max(scan_date) from 
  table_name group by  document_type)

Upvotes: 0

Related Questions