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!
Advertisement
Answer
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;