13 Replies Latest reply: May 2, 2012 3:42 AM by jeneesh RSS

    Count from START

    Chloe_19
      My previous question was:

      >
      I want ONLY the IDs which:
      * have "LAP" Between 0901-1033
      * have "UNIT" count as 1
      >

      Answer was:

      >
      select * from tablename t
      where to_number(lap) between 903 and 1033
      and 1>=(select count(*) from tablename where id=t.id);

      Thanks to Kiran
      >

      Now I would like all the ID who STARTED in range of 0901-1033
      ID     UNIT     LAP
      1     POL     908
      1     OOP     1014
      2     LOP     1002
      2     SIN     1033
      2     OOP     1104
      3     UTG     1201
      3     FER     1203
      SO:

      ID 1 = YES LAP in range
      ID 2 = YES (even though 1104 is out of range) the ID started in range values
      ID 3= NO LAP started in 1201 (not in acceptable range)
        • 1. Re: Count from START
          Lokanath Giri
          WHERE LAP BETWEEN 0901 AND 1033
          As per your data
          with t
          as
          ( select 1 ID,  'POL' UNIT,908 LAP from dual union all 
            select 1,  'OOP' UNIT , 1014 from dual union all 
            select 2,  'LOP' UNIT ,1002 from dual union all 
            select 2,  'SIN' UNIT ,1033 from dual union all 
            select 2,  'OOP' UNIT ,1104 from dual union all 
            select 3,  'UTG' UNIT ,1201 from dual union all 
            select 3,  'FER' UNIT ,1203 from dual 
          )
          SELECT DISTINCT(ID)
          FROM t WHERE LAP BETWEEN 0901 AND 1033
          /
                  ID
          ----------
                   1
                   2
          Edited by: Lokanath Giri on ३० अप्रैल, २०१२ १२:३१ अपराह्न
          • 2. Re: Count from START
            Chloe_19
            WHERE LAP BETWEEN 0901 AND 1033
            That will give me only the IDs who were involved between those LAP
            It wont give me highers LAP values

            Need it to be START in that range not limit to that range.
            • 3. Re: Count from START
              Lokanath Giri
              This is the out put you expected right ?
              >
              Chloe_19 wrote:      
              SO:

              ID 1 = YES LAP in range
              ID 2 = YES (even though 1104 is out of range) the ID started in range values
              ID 3= NO LAP started in 1201 (not in acceptable range)
              >

              >
              Chloe_19 wrote:

                   
              That will give me only the IDs who were involved between those LAP
              It wont give me highers LAP values

              Need it to be START in that range not limit to that range.

              >

              Is this you need ?
              with t
              as
              ( select 1 ID,  'POL' UNIT,908 LAP from dual union all
                select 1,  'OOP' UNIT , 1014 from dual union all
                select 2,  'LOP' UNIT ,1002 from dual union all
                select 2,  'SIN' UNIT ,1033 from dual union all
                select 2,  'OOP' UNIT ,1104 from dual union all
                select 3,  'UTG' UNIT ,1201 from dual union all
                select 3,  'FER' UNIT ,1203 from dual
              )
              SELECT ID,CASE WHEN min_lap BETWEEN 0901 AND 1033 THEN
                                           CASE WHEN max_lap  BETWEEN 0901 AND 1033 THEN
                                                'Yes' 
                                            ELSE
                                                'Yes'||'(out of range '||max_lap||')'
                                            END 
                           ELSE
                             'No' 
                           END Result                             
              FROM
              (                           
              SELECT ID, min(LAP) min_lap ,  max(LAP) max_lap FROM t 
              GROUP BY ID
              )
              /
                      ID RESULT
              ---------- ----------------------------------------------------------
                       1 Yes
                       2 Yes(out of range 1104)
                       3 No
               
              Many OPs have given variety of solutions so use your logic to get as you expect !!!

              Edited by: Lokanath Giri on ३० अप्रैल, २०१२ २:३० अपराह्न
              • 4. Re: Count from START
                Himanshu Binjola
                Hope below helps you
                WITH T AS
                ( SELECT 1 ID,  'POL' UNIT,908 LAP FROM DUAL UNION ALL 
                  SELECT 1,  'OOP' UNIT , 1014 FROM DUAL UNION ALL 
                  SELECT 2,  'LOP' UNIT ,1002 FROM DUAL UNION ALL 
                  SELECT 2,  'SIN' UNIT ,1033 FROM DUAL UNION ALL 
                  SELECT 2,  'OOP' UNIT ,1104 FROM DUAL UNION ALL 
                  SELECT 3,  'UTG' UNIT ,1201 FROM DUAL UNION ALL 
                  SELECT 3,  'FER' UNIT ,1203 FROM DUAL 
                ),
                 T_ORDERED AS
                ( SELECT ID,UNIT, LAP, ROW_NUMBER() OVER( PARTITION BY ID ORDER BY to_number(LAP) DESC NULLS LAST) RN
                  FROM T t
                  WHERE EXISTS ( SELECT NULL
                                 FROM T t1
                                 WHERE t.ID = t1.ID 
                                 AND TO_NUMBER(t1.lap) BETWEEN 901 AND 1033 
                                )
                )      
                SELECT ID,UNIT, LAP
                FROM T_ORDERED 
                WHERE RN = 1
                
                
                        ID UNIT        LAP
                ---------- --- ----------
                         1 OOP       1014
                         2 OOP       1104
                {code}
                
                Edited by: Himanshu Binjola on Apr 30, 2012 12:35 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                • 5. Re: Count from START
                  Chloe_19
                  Sorry you all misundertand.
                  with t
                  as
                  ( select 1 ID,  'POL' UNIT,0908 LAP from dual union all 
                    select 1,  'OOP' UNIT ,1014 from dual union all 
                    select 1,  'OIP' UNIT ,0801 from dual union all 
                    select 2,  'LOP' UNIT ,1002 from dual union all 
                    select 2,  'SIN' UNIT ,1033 from dual union all 
                    select 2,  'OOP' UNIT ,1104 from dual union all 
                    select 3,  'UTG' UNIT ,1032 from dual union all 
                    select 3,  'FER' UNIT ,1203 from dual 
                  )
                  SELECT DISTINCT ID
                  FROM t WHERE LAP BETWEEN 0901 AND 1033
                  gives me:
                  ID
                  1
                  2
                  3 
                  it should NOT pick up ID 1 because of
                    select 1,  'OIP' UNIT ,0801 from dual union all 
                  which is lower then the range, which means the student started in 0801
                  • 6. Re: Count from START
                    jeneesh
                    Chloe_19 wrote:
                    Sorry you all misundertand.
                    with t
                    as
                    ( select 1 ID,  'POL' UNIT,0908 LAP from dual union all 
                    select 1,  'OOP' UNIT ,1014 from dual union all 
                    select 1,  'OIP' UNIT ,0801 from dual union all 
                    select 2,  'LOP' UNIT ,1002 from dual union all 
                    select 2,  'SIN' UNIT ,1033 from dual union all 
                    select 2,  'OOP' UNIT ,1104 from dual union all 
                    select 3,  'UTG' UNIT ,1032 from dual union all 
                    select 3,  'FER' UNIT ,1203 from dual 
                    )
                    SELECT DISTINCT ID
                    FROM t WHERE LAP BETWEEN 0901 AND 1033
                    gives me:
                    ID
                    1
                    2
                    3 
                    it should NOT pick up ID 1 because of
                    select 1,  'OIP' UNIT ,0801 from dual union all 
                    which is lower then the range, which means the student started in 0801
                    SQL> with t
                      2  as
                      3  ( select 1 ID,  'POL' UNIT,0908 LAP from dual union all
                      4    select 1,  'OOP' UNIT ,1014 from dual union all
                      5    select 1,  'OIP' UNIT ,0801 from dual union all
                      6    select 2,  'LOP' UNIT ,1002 from dual union all
                      7    select 2,  'SIN' UNIT ,1033 from dual union all
                      8    select 2,  'OOP' UNIT ,1104 from dual union all
                      9    select 3,  'UTG' UNIT ,1032 from dual union all
                     10    select 3,  'FER' UNIT ,1203 from dual
                     11  )
                     12  SELECT ID
                     13  FROM t
                     14  group by id
                     15  having min(lap) BETWEEN 0901 AND 1033;
                    
                            ID
                    ----------
                             2
                             3
                    • 7. Re: Count from START
                      Sven W.
                      jeneesh wrote:
                      SQL>
                      ...
                      12 SELECT ID
                      13 FROM t
                      14 group by id
                      15 having min(lap) BETWEEN 0901 AND 1033;

                      ID
                      ----------
                      2
                      3

                      This was the first sensible query I've read in this whole thread! Everybody who used DISTINCT or similar evil constructs should be ashamed of themselfs.
                      And everybody who used a subquery should read up about the having clause. Analytic functions are nice, but tend to be overused, if a grouping operation is required.
                      • 8. Re: Count from START
                        849776
                        SELECT id,
                               unit,
                               lap,
                               CASE WHEN lap BETWEEN 0901 AND 1033 THEN 'YES' ELSE  'NO' END category
                          FROM (SELECT 1 ID, 'POL' UNIT, 908 LAP FROM DUAL
                                UNION ALL
                                SELECT 1, 'POL', 908 FROM DUAL
                                UNION ALL
                                SELECT 1, 'OOP', 1014 FROM DUAL
                                UNION ALL
                                SELECT 2, 'LOP', 1002 FROM DUAL
                                UNION ALL
                                SELECT 2, 'SIN', 1033 FROM DUAL
                                UNION ALL
                                SELECT 2, 'OOP', 1104 FROM DUAL
                                UNION ALL
                                SELECT 3, 'UTG', 1201 FROM DUAL
                                UNION ALL
                                SELECT 3, 'FER', 1203 FROM DUAL);
                        • 9. Re: Count from START
                          jeneesh
                          Sven W. wrote:
                          >
                          This was the first sensible query I've read in this whole thread! Everybody who used DISTINCT or similar evil constructs should be ashamed of themselfs.
                          And everybody who used a subquery should read up about the having clause. Analytic functions are nice, but tend to be overused, if a grouping operation is required.
                          :)
                          • 10. Re: Count from START
                            Lokanath Giri
                            >
                            Sven W. wrote:

                            Posts: 6,121
                            Registered: 10/13/00
                                 
                            Re: Count from START
                            Posted: 30 अप्रैल, 2012 11:54 अपराह्न

                            jeneesh wrote:

                            SQL></div>
                            ...
                            <div class="jive-quote"> 12 SELECT ID
                            13 FROM t
                            14 group by id
                            15 having min(lap) BETWEEN 0901 AND 1033;

                            ID
                            ----------
                            2
                            3


                            This was the first sensible query I've read in this whole thread! Everybody who used DISTINCT or similar evil constructs should be ashamed of themselfs.
                            And everybody who used a subquery should read up about the having clause. Analytic functions are nice, but tend to be overused, if a grouping operation is required.
                            >

                            I have still doubt about yours' statement. I found prior looks more efficient :-)
                            SQL - LOG2KOR@ORCL> with t
                              2  as
                              3  ( select 1 ID,  'POL' UNIT,908 LAP from dual union all 
                              4    select 1,  'OOP' UNIT , 1014 from dual union all 
                              5    select 2,  'LOP' UNIT ,1002 from dual union all 
                              6    select 2,  'SIN' UNIT ,1033 from dual union all 
                              7    select 2,  'OOP' UNIT ,1104 from dual union all 
                              8    select 3,  'UTG' UNIT ,1201 from dual union all 
                              9    select 3,  'FER' UNIT ,1203 from dual 
                             10  )
                             11  SELECT DISTINCT(ID)
                             12  FROM t WHERE LAP BETWEEN 0901 AND 1033
                             13  / 
                            
                                    ID
                            ----------
                                     1
                                     2
                            
                            
                            Execution Plan
                            ----------------------------------------------------------
                               0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=7 Bytes=42)
                               1    0   HASH (UNIQUE) (Cost=9 Card=7 Bytes=42)
                               2    1     VIEW (Cost=8 Card=7 Bytes=42)
                               3    2       UNION-ALL
                               4    3         FAST DUAL (Cost=2 Card=1)
                               5    3         FAST DUAL (Cost=2 Card=1)
                               6    3         FAST DUAL (Cost=2 Card=1)
                               7    3         FAST DUAL (Cost=2 Card=1)
                               8    3         FILTER
                               9    8           FAST DUAL (Cost=2 Card=1)
                              10    3         FILTER
                              11   10           FAST DUAL (Cost=2 Card=1)
                              12    3         FILTER
                              13   12           FAST DUAL (Cost=2 Card=1)
                            
                            
                            
                            
                            Statistics
                            ----------------------------------------------------------
                                      1  recursive calls
                                      0  db block gets
                                      0  consistent gets
                                      0  physical reads
                                      0  redo size
                                    455  bytes sent via SQL*Net to client
                                    508  bytes received via SQL*Net from client
                                      2  SQL*Net roundtrips to/from client
                                      0  sorts (memory)
                                      0  sorts (disk)
                                      2  rows processed
                            
                            SQL - LOG2KOR@ORCL> with t
                              2  as
                              3  ( select 1 ID,  'POL' UNIT,908 LAP from dual union all 
                              4    select 1,  'OOP' UNIT , 1014 from dual union all 
                              5    select 2,  'LOP' UNIT ,1002 from dual union all 
                              6    select 2,  'SIN' UNIT ,1033 from dual union all 
                              7    select 2,  'OOP' UNIT ,1104 from dual union all 
                              8    select 3,  'UTG' UNIT ,1201 from dual union all 
                              9    select 3,  'FER' UNIT ,1203 from dual 
                             10  )
                             11  SELECT ID
                             12  FROM t  group by ID having min(LAP) BETWEEN 0901 AND 1033
                             13  / 
                            
                                    ID
                            ----------
                                     1
                                     2
                            
                            
                            Execution Plan
                            ----------------------------------------------------------
                               0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=15 Card=7 Bytes=42
                                      )
                            
                               1    0   FILTER
                               2    1     HASH (GROUP BY) (Cost=15 Card=7 Bytes=42)
                               3    2       VIEW (Cost=14 Card=7 Bytes=42)
                               4    3         UNION-ALL
                               5    4           FAST DUAL (Cost=2 Card=1)
                               6    4           FAST DUAL (Cost=2 Card=1)
                               7    4           FAST DUAL (Cost=2 Card=1)
                               8    4           FAST DUAL (Cost=2 Card=1)
                               9    4           FAST DUAL (Cost=2 Card=1)
                              10    4           FAST DUAL (Cost=2 Card=1)
                              11    4           FAST DUAL (Cost=2 Card=1)
                            
                            
                            
                            
                            Statistics
                            ----------------------------------------------------------
                                      1  recursive calls
                                      0  db block gets
                                      0  consistent gets
                                      0  physical reads
                                      0  redo size
                                    455  bytes sent via SQL*Net to client
                                    508  bytes received via SQL*Net from client
                                      2  SQL*Net roundtrips to/from client
                                      0  sorts (memory)
                                      0  sorts (disk)
                                      2  rows processed
                            • 11. Re: Count from START
                              jeneesh
                              Lokanath Giri wrote:
                              I have still doubt about yours' statement. I found prior looks more efficient :-)
                              SQL - >  11  SELECT DISTINCT(ID)
                              12  FROM t WHERE LAP BETWEEN 0901 AND 1033
                              13  / 
                              
                              ID
                              ----------
                              1
                              2
                              
                              
                              SQL - >   SELECT ID
                              12  FROM t  group by ID having min(LAP) BETWEEN 0901 AND 1033
                              13  / 
                              But the above 2 queries will no way give same results
                              • 12. Re: Count from START
                                Lokanath Giri
                                >
                                jeneesh wrote:

                                SQL - > 11 SELECT DISTINCT(ID)
                                12 FROM t WHERE LAP BETWEEN 0901 AND 1033
                                13 /

                                ID
                                ----------
                                1
                                2


                                SQL - > SELECT ID
                                12 FROM t group by ID having min(LAP) BETWEEN 0901 AND 1033
                                13 /

                                But the above 2 queries will no way give same results
                                >
                                You may be right.
                                Good practice is always put your points with some data. :-)


                                Good jinesh I understood. Thanks :-)
                                • 13. Re: Count from START
                                  jeneesh
                                  Lokanath Giri wrote:
                                  You may be right.
                                  Good practice is always put your points with some data. :-)
                                  The meaning of the queries itself is different. That is why I did not put any samples.

                                  Onew query is taking all the IDs which are having a LAP between a range. other query is taking all those IDs whose min(LAP) is between a range.
                                  SQL> with t as (
                                    2  SELECT 1 ID, 'POL' UNIT, 908 LAP FROM DUAL
                                    3  UNION ALL
                                    4  SELECT 1, 'POL', 100 FROM DUAL)
                                    5  SELECT DISTINCT(ID)
                                    6  FROM t WHERE LAP BETWEEN 0901 AND 1033;
                                  
                                          ID
                                  ----------
                                           1
                                  
                                  SQL> with t as (
                                    2  SELECT 1 ID, 'POL' UNIT, 908 LAP FROM DUAL
                                    3  UNION ALL
                                    4  SELECT 1, 'POL', 100 FROM DUAL)
                                    5  SELECT ID
                                    6  FROM t  group by ID having min(LAP) BETWEEN 0901 AND 1033;
                                  
                                  no rows selected