13 Replies Latest reply on May 2, 2012 8:42 AM by jeneesh

# Count from START

My previous question was:

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

>
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
``WHERE LAP BETWEEN 0901 AND 1033``
``````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
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
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
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
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
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
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
``````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
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
>
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  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  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
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
>
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
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``````