OraFAQ Forum: SQL & PL/SQL » LAG & LEAD Question

Home » SQL & PL/SQL » SQL & PL/SQL » LAG & LEAD Question (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

LAG & LEAD Question [message #603398]

Sun, 15 December 2013 20:47

venky83
Messages: 1
Registered: December 2013

Junior Member

Lets say I have a table A with Column A containing data as follows

COLUMN A
100
150
200
220
240

And my query should be in such a way, that if the input is 205(which is not existing in the column), then my output should be the previous value and next value present in the column i.e. 200 and 220.

Similarly if i give any input between 101 and 149, then the output will be 100 and 150.

It is ok to get NULL if the input is 240+.

I tried with LAG AND LEAD but the function is expecting the input to be present in the column whereas I cannot have that.

Thanks
Venkat

Report message to a moderator

Re: LAG & LEAD Question [message #603399 is a reply to message #603398]

Sun, 15 December 2013 21:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: LAG & LEAD Question [message #603403 is a reply to message #603398]

Mon, 16 December 2013 00:21

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select max(cola) from mytab where cola < &&input
union all
select min(cola) from mytab where cola > &&input
/

Report message to a moderator

Re: LAG & LEAD Question [message #603409 is a reply to message #603403]

Mon, 16 December 2013 01:13

gauravgautam135
Messages: 33
Registered: December 2013

Member

If you are looking into both values of your range into same row then you can try below scenario:

WITH mytab AS
(
SELECT 100 col1 FROM dual
UNION ALL
SELECT 150 col1 FROM dual
UNION ALL
SELECT 200 col1 FROM dual
UNION ALL
SELECT 220 col1 FROM dual
UNION ALL
Select 240 col1 FROM dual
)
SELECT MAX(t1.col1),MIN(t2.col1) 
  FROM mytab t1, mytab t2 
 where t1.col1 < &&val 
   AND t2.col1 > &&val

This will give 200 and 220 when user input 205 as value.
However, when you pass value which is present in your table (i.e. 220) then above query will give you 200 and 240 as output.
If you want to have 220 and 220 as your range for your data which is present in table then use "=" sign with ">" sign as shown below:

WITH mytab AS
(
SELECT 100 col1 FROM dual
UNION ALL
SELECT 150 col1 FROM dual
UNION ALL
SELECT 200 col1 FROM dual
UNION ALL
SELECT 220 col1 FROM dual
UNION ALL
Select 240 col1 FROM dual
)
SELECT MAX(t1.col1),MIN(t2.col1) 
  FROM mytab t1, mytab t2 
 where t1.col1 <= &&val 
   AND t2.col1 >= &&val

Similarly if you provide value which is less than 100 or greater than 240 then above code will return NULL for your range values.

Report message to a moderator

Re: LAG & LEAD Question [message #603657 is a reply to message #603398]

Wed, 18 December 2013 04:08

tigsav
Messages: 49
Registered: April 2012

Member

Hi,

This is a similar solution to above.

SELECT MAX(b.id) ,'LOW_BOUND' AS BOUNDARY  FROM faq_1 b where id <=(&input_num) UNION SELECT MIN(id)  ,'HIGH_BOUND'  AS BOUNDARY  FROM faq_1 a where id >=(&input_num);

Only difference is if the number is greater than 240 it will show lower bound as 240 and upper bound null and vice versa if the number is less than 100.
Just interchange the table and column names .

Report message to a moderator

Previous Topic:	Problem in SQL report
Next Topic:	update sql script

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 08:26:19 CDT 2024