1 2 Previous Next 16 Replies Latest reply: Feb 10, 2010 7:25 PM by Aketi Jyuuzou Go to original post RSS
      • 15. Re: SQL - Find Continuous Records
        752564
        Gomzi,

        I am not sure that I understand your question. In the example that I provided at the beginning of the thread, I set the text to bold to illustrate what I was looking for from the query, the text is not bold in the database table. If you could clarify your statement, I might be able to help you better.

        Kyle
        • 16. Re: SQL - Find Continuous Records
          Aketi Jyuuzou
          If we want rows whose count(*)=1,we can use below solution ;-)
          select id,lith,nullif(cnt,1) as cnt
          from (select id,lith,count(*) as cnt,
                Row_Number() over(partition by id order by count(*) desc) as rn
                from (select id,lith,
                       Row_Number() over(partition by id      order by depth)
                      -Row_Number() over(partition by id,lith order by depth) dis
                        from tab)
                group by id,lith,dis)
          where cnt > 1 or rn = 1;
          
          ID   LITH   CNT
          ---  ----  ----
          1-1  COAL  null
          2-2  COAL     4
          2-2  COAL     2
          1 2 Previous Next