我的表包含重复的行，如何显示一个？ [英] My table contains duplicate rows, how to show one?

问题描述

除了我以前的问题，这个表（从最后一个问题展开）包含重复的行

Further to my previous question, this table (expanded from the last question) contains duplicate rows

 titel                             | interpret        | jahr
-----------------------------------+------------------+-----
 Beauty                            | Ryuichi Sakamoto | 1990
 Goodbye Country (Hello Nightclub) | Groove Armada    | 2001
 Glee                              | Bran Van 3000    | 1997
 Glee                              | Bran Van 3000    | 1997
 Beauty                            | Ryuichi Sakamoto | 1990
 Goodbye Country (Hello  Nightclub)| Groove Armada    | 2001
 Glee                              | Groove Armada    | 2001  

我将如何获取每组重复行的一个示例？ （最后一行完全由重复的列组成，但不是重复的行）。

how would I SELECT to get one example of each set of duplicate rows? (the final row consists entirely of duplicate columns, but is not a duplicate row).

IE，结果将返回

| Beauty                            | Ryuichi Sakamoto | 1990
| Goodbye Country (Hello Nightclub) | Groove Armada    | 2001
| Glee                              | Bran Van 3000    | 1997

推荐答案

您可以：

select distinct titel, interpret, jahr from tablename

要获得编辑的结果，您可以这样做一些有趣的事情：

To get the edited result, you could do something rather interesting like this:

select * 
from table1 
where rowid in 
(
    -- get the first row of each distinct titel
    select min(sr) 
    from 
    (select distinct titel from table1) a 
    inner join 
    (
        -- get first row number for each distinct titel/interpret/jahr
        select min(rowid) as sr, titel, interpret, jahr 
        from table1 
        group by titel, interpret, jahr
    ) b 
        on a.titel = b.titel 
    group by 
        a.titel
);

结果如下所示：

titel                            |interpret       |jahr
Beauty                           |Ryuichi Sakamoto|1990
Goodbye Country (Hello Nightclub)|Groove Armada   |2001
Glee                             |Bran Van 3000   |1997

这篇关于我的表包含重复的行，如何显示一个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！