这里有包含这样的数据的表:
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
现在,我只想要行号最少的行:
我想要这样
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
我只想删除基于最高价值的重复编号,我已经自己尝试了许多,但没有任何效果。
帮我解决这个问题,在此先感谢。
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
谢谢你的回复@marcothesane其工作