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

Question

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!

Answer 1

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;