CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Jared Holmes
Jared Holmes

Reputation: 1

Removing Entire record based on duplicate values in a specific field

Here is the query I am using:

Select count(DLOCATION), DLOCATION, DPOSTAL
FROM BURNABYFINAL
Group by DLOCATION, DPOSTAL
order by count(dlocation) desc;

I would like to remove the entire record that has duplicate "Dpostal" in it. I have tried using the distinct function but it results in an error due to the group by function. Any help would be much appreciated.

I have added an image. I would only like to keep the record with the highest "count(location)". The other occurrences should be deleted.

The duplicate postal code value example is highlighted in yellow

Upvotes: 0

Views: 58

Answers (2)

Popeye
Popeye

Reputation: 35900

You can use the RANK function as follows:

SELECT SUM(CNT_DLOCATION), DLOCATION, DPOSTAL
FROM
(Select count(DLOCATION) CNT_DLOCATION, DLOCATION, DPOSTAL, 
       RANK() OVER (PARTITION BY DPOSTAL ORDER BY count(DLOCATION) DESC NULLS LAST) AS RNK
FROM BURNABYFINAL
Group by DLOCATION, DPOSTAL)
WHERE RNK = 1
GROUP BY DLOCATION, DPOSTAL
order by CNT_DLOCATION desc;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

Rank rows by counts, and use it for the final result.

  • lines #1 - 11 represent sample data (I was lazy to type everything)
  • lines #12 - 17 represent that ranking I was talking about
  • lines #18 onward fetch the final result by filtering out everything that's not ranked as #1
SQL> with burnabyfinal (dlocation, dpostal) as
  2    (select '3935 Kincaid St Burnaby', 'V5G 2X6' from dual union all
  3     select '3935 Kincaid St Burnaby', 'V5G 2X6' from dual union all
  4     select '3935 Kincaid St Burnaby', 'V5G 2X6' from dual union all
  5     select '1111 Dont want'         , 'V5G 2X6' from dual union all
  6     --
  7     select '203 Sunset'             , 'A4F 2CS' from dual union all
  8     --
  9     select '200 Hastings'           , 'V92 S9V' from dual union all
 10     select '300 Hastings 2nd'       , 'V92 S9V' from dual
 11    ),
 12  temp as
 13    (select count(dlocation) cnt, dlocation, dpostal,
 14       dense_rank() over (partition by dpostal order by count(*) desc) rnk
 15     from burnabyfinal
 16     group by dlocation, dpostal
 17    )
 18  select cnt, dlocation, dpostal
 19  from temp
 20  where rnk = 1
 21  order by cnt desc;

       CNT DLOCATION               DPOSTAL
---------- ----------------------- -------
         3 3935 Kincaid St Burnaby V5G 2X6
         1 300 Hastings 2nd        V92 S9V
         1 200 Hastings            V92 S9V
         1 203 Sunset              A4F 2CS

SQL>

Upvotes: 1

Related Questions