Forum Stats

  • 3,839,099 Users
  • 2,262,455 Discussions
  • 7,900,863 Comments

Discussions

SQL - Find Continuous Records

2»

Answers

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Feb 10, 2010 9:02PM
    I have used Tabibitosan method B-)
    1007478
    with tab as (
    select '1-1' id,'SAND' lith,150 depth from dual union
    select '1-1' id,'COAL' lith,200 depth from dual union
    select '1-1' id,'SAND' lith,250 depth from dual union
    select '1-1' id,'COAL' lith,300 depth from dual union
    select '2-2' id,'SAND' lith, 75 depth from dual union
    select '2-2' id,'COAL' lith,100 depth from dual union
    select '2-2' id,'COAL' lith,150 depth from dual union
    select '2-2' id,'COAL' lith,200 depth from dual union
    select '2-2' id,'COAL' lith,250 depth from dual union
    select '2-2' id,'SAND' lith,300 depth from dual union
    select '2-2' id,'COAL' lith,400 depth from dual union
    select '2-2' id,'COAL' lith,450 depth from dual)
    select id,lith,count(*)
    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
    having count(*) > 1;
    
    ID   LITH   COUNT(*)
    ---  ----  ---------
    2-2  COAL          2
    2-2  COAL          4
  • cd_2
    cd_2 Member Posts: 5,021
  • 752564
    752564 Member Posts: 6
    Wow, very Nice!
  • 750281
    750281 Member Posts: 245
    hi kyle,
    could u tell me the bold item in table , is it outside the database ?? what exactly it is?? how u put data in bold letter in table in oracle database??

    thnx...
  • 752564
    752564 Member Posts: 6
    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
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
This discussion has been closed.