Retrieve only the lowest Row from the duplicates

Question

Here I have the table which contains data like this:

   Location    No   Days
   ----------------------
      Callao    1    7
      Callao    2    7
      CHENNAI   3    6
      SINGAPORE 4   30
      SINGAPORE 5    7
      SINGAPORE 6    7
    LOS ANGELES 7    9
   HONG KONG    7   11
   HONG KONG    7    6
 LOS ANGELES    8    6
   HONG KONG    9    6
   HONG KONG    9    4
 LOS ANGELES    9   10
 LOS ANGELES    9    9
 LOS ANGELES    10   6

Here now I only want the row which has number with lowest days:

I want it like this,

   Location    No   Days
   ---------------------
      Callao    1    7
      Callao    2    7
     CHENNAI    3    6
    SINGAPORE   4   30
   SINGAPORE    5    7
   SINGAPORE    6    7
   HONG KONG    7    6
 LOS ANGELES    8    6
   HONG KONG    9    4
 LOS ANGELES    10   6

I only want to remove duplication No. based on the highest value, I already tried many on my own but nothing is working.

Help me solve this, thanks in advance.

score 0 · Accepted Answer · answered Nov 30 '20 at 02:49

WITH
-- your input, thanks for pasting it in ..
indata(location,no,days) AS (
          SELECT 'Callao',1,7
UNION ALL SELECT 'Callao',2,7
UNION ALL SELECT 'CHENNAI',3,6
UNION ALL SELECT 'SINGAPORE',4,30
UNION ALL SELECT 'SINGAPORE',5,7
UNION ALL SELECT 'SINGAPORE',6,7
UNION ALL SELECT 'LOS ANGELES',7,9
UNION ALL SELECT 'HONG KONG',7,11
UNION ALL SELECT 'HONG KONG',7,6
UNION ALL SELECT 'LOS ANGELES',8,6
UNION ALL SELECT 'HONG KONG',9,6
UNION ALL SELECT 'HONG KONG',9,4
UNION ALL SELECT 'LOS ANGELES',9,10
UNION ALL SELECT 'LOS ANGELES',9,9
UNION ALL SELECT 'LOS ANGELES',10,6
)
-- real query starts here, replace "," with "WITH" ..
,
w_filter AS (
  SELECT
    *
  , ROW_NUMBER() OVER(PARTITION BY no ORDER BY days) AS fil
  FROM indata
)
SELECT
  location
, no
, days
FROM w_filter
WHERE fil=1
ORDER BY 2 ;
-- out   location   | no | days 
-- out -------------+----+------
-- out  Callao      |  1 |    7
-- out  Callao      |  2 |    7
-- out  CHENNAI     |  3 |    6
-- out  SINGAPORE   |  4 |   30
-- out  SINGAPORE   |  5 |    7
-- out  SINGAPORE   |  6 |    7
-- out  HONG KONG   |  7 |    6
-- out  LOS ANGELES |  8 |    6
-- out  HONG KONG   |  9 |    4
-- out  LOS ANGELES | 10 |    6

Retrieve only the lowest Row from the duplicates

1 Answers1