CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-sqldeveloper
JNK123
JNK123

Reputation: 3

Oracle 11g SQL Query - Specify which duplicates to Exclude from Select Statement

I have a question about excluding duplicate values with my Oracle SQL query. Using select distinct does not work in this case. I only want to select one row for each event#, the row where Mobile = Yes.

Actual Result of Current Query:

State   Town        Event#  Date        Username    Mobile?
MA      WATERTOWN   1       24-Jun-21   jappleseed  N
MA      WATERTOWN   1       24-Jun-21   mobile      Y
MA      WATERTOWN   2       24-Jun-21   jsmith      N
MA      WATERTOWN   3       24-Jun-21   mobile      Y
MA      WATERTOWN   4       24-Jun-21   mobile      Y

Desired Result:

State   Town        Event#  Date        Username    Mobile? 
MA      WATERTOWN   1       24-Jun-21   mobile      Y
MA      WATERTOWN   2       24-Jun-21   jsmith      N
MA      WATERTOWN   3       24-Jun-21   mobile      Y
MA      WATERTOWN   4       24-Jun-21   mobile      Y

In this ideal result, one of the rows for Event# 1 has not been selected - the one kept is where Mobile = Y. Is it possible to exclude a duplicate, but control which row is kept and which is not? (In this case, I want to keep the row where Mobile = Y in the result). The solution cannot be specific to this one Event#, as this is just a small excerpt/example of the data from a table with thousands of rows, and hundreds of cases where these duplicates occur. Deleting records from the table is not an option either.

Current SQL Query:

SELECT DISTINCT
        STATE           
       ,TOWN           
       ,EVENT#
       ,DATE            
       ,USERNAME      
       ,MOBILE           
FROM    EVENT_DETAILS
WHERE   DATE >= sysdate -365 
AND     TOWN = 'WATERTOWN'

ORDER BY
        EVENT# DESC

Any advice will be greatly appreciated!

Upvotes: 0

Views: 103

Answers (3)

Atif
Atif

Reputation: 2210

You can also use below query without using row_number to get the desired result.

WITH CTE1 AS 
(SELECT * FROM TABLE1 WHERE "Mobile" = 'Y')
SELECT * FROM TABLE1 A WHERE NOT EXISTS(SELECT 1 FROM CTE1 B
                                        WHERE A."Event" = B."Event")
UNION
SELECT * FROM CTE1

DB_FIDDLE

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use row_number(), but you have to be careful about filtering. I would recommend:

select *         
from (select ed.* ,
             row_number() over (partition by town, event# order by mobile desc) as seqnum
      from event_details ed
      where date >= sysdate -365 AND
           town = 'WATERTOWN'
     ) ed
where seqnum = 1
order by EVENT# desc;

Note that you should not need select distinct in the outer query either.

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

I think you can use a window function here -

SELECT DISTINCT STATE           
               ,TOWN           
               ,EVENT#
               ,DATE            
               ,USERNAME      
               ,MOBILE           
  FROM (SELECT STATE           
              ,TOWN           
              ,EVENT#
              ,DATE            
              ,USERNAME      
              ,MOBILE  
              ,ROW_NUMBER() OVER(PARTITION BY EVENT# ORDER BY MOBILE DESC) RN
          FROM EVENT_DETAILS)
 WHERE DATE >= sysdate -365
   AND TOWN = 'WATERTOWN'
   AND RN = 1
 ORDER BY EVENT# DESC;

Upvotes: 1

Related Questions