在oraclesql中仅基于一列返回不同的行

Question

我想返回n个不同的行。不同的行应仅基于一列(SN(。

我有下面的查询，预计会返回4行，其中序列号大于2，并且不会返回具有类似SN列值的行。

表

SN  letter value
1   test   25
1   bread  26
3   alpha  43
4   beta   23
4   gamma  5
5   omega  60
6   omega  60

预期结果

SN  letter value
3   alpha  43
4   beta   23
5   omega  60
6   omega 60

这是我的疑问。这不能正常工作，它返回重复的值，因为它按所有列组合而不是仅按单列SN.筛选disctinct值

SELECT * FROM (SELECT a.*, row_number() over(order by SN) rowRank 
FROM (SELECT distinct SN, letter, value from table where SN > 2 order by SN) a) 
WHERE rowRank BETWEEN 1 AND 4}"

Answer 1

在尝试筛选出结果之前，不需要使用DISTINCT。如果需要修改应返回SN的哪个副本，可以修改row_rank分析函数的ORDER BY子句。现在它按字母顺序返回第一个LETTER值，因为它与您的示例结果相匹配。

查询

WITH
some_table (sn, letter, VALUE)
AS
(SELECT 1, 'test', 25 FROM DUAL
UNION ALL
SELECT 1, 'bread', 26 FROM DUAL
UNION ALL
SELECT 3, 'alpha', 43 FROM DUAL
UNION ALL
SELECT 4, 'beta', 23 FROM DUAL
UNION ALL
SELECT 4, 'gamma', 5 FROM DUAL
UNION ALL
SELECT 5, 'omega', 60 FROM DUAL
UNION ALL
SELECT 6, 'omega', 60 FROM DUAL)
--Above is to set up the sample data. Use the query below with your real table
SELECT sn, letter, VALUE
FROM (SELECT sn,
letter,
VALUE,
ROW_NUMBER () OVER (PARTITION BY sn ORDER BY letter)     AS row_rank
FROM some_table
WHERE sn > 2)
WHERE row_rank = 1
ORDER BY sn
FETCH FIRST 4 ROWS ONLY;

结果

SN    LETTER    VALUE
_____ _________ ________
3 alpha           43
4 beta            23
5 omega           60
6 omega           60

Answer 2

SELECT * FROM 
(
SELECT 
t.* 
,ROW_NUMBER() OVER (PARTITION BY sn ORDER BY value ) rn
FROM 
t
WHERE sn > 2
) t1
WHERE t1.rn = 1
ORDER BY sn;