Home » SQL & PL/SQL » SQL & PL/SQL » Analytic Functions - LAG
Analytic Functions - LAG [message #222951] Wed, 07 March 2007 01:05 Go to next message
victoryhendry
Messages: 95
Registered: January 2007
Location: Bangalore
Member

where we will use
1)partition by column_name1 order by column_name2
where we use this keyword in SQL lag(column_name) [message #222953 is a reply to message #222951] Wed, 07 March 2007 01:07 Go to previous messageGo to next message
victoryhendry
Messages: 95
Registered: January 2007
Location: Bangalore
Member

where we use this keyword in SQL lag(column_name)

lag keyword
can u explain this query? I dont know use of source,lag,partition keyword? [message #222956 is a reply to message #222951] Wed, 07 March 2007 01:11 Go to previous messageGo to next message
victoryhendry
Messages: 95
Registered: January 2007
Location: Bangalore
Member

can u explain this query? I dont know use of source,lag,partition keyword?

why where we will use source key word?

with source as
(select 'TamilNadu' state,'chennai' city from dual union all
select 'Karnataka' state,'Mangalore' city from dual)

select case when state=prev_state then null
else state
end ||' '||city state_city
from (select state
,lag(state) over (partition by state order by city) prev_state
,city
from source)
Re: can u explain this query? I dont know use of source,lag,partition keyword? [message #222960 is a reply to message #222956] Wed, 07 March 2007 01:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Clearly, lag and partition belong together, so you google for oracle lag partition.
This will return you more than enough hits to find what you need.

The "with ... as" is called the factoring clause. Google for oracle factoring clause and again, plenty of hits.

I admit, the second one was a bit nastier than the first, but really, you have to get used to trying to find these things yourself. It will be a big help!
Re: can u explain this query? I dont know use of source,lag,partition keyword? [message #222985 is a reply to message #222956] Wed, 07 March 2007 02:18 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Victoryhendry - If you didn't understand the solution I posted to your other question, why didn't you just ask for an explanation on that thread, instead of starting a new one.

[edited to add url]

[Updated on: Wed, 07 March 2007 02:21]

Report message to a moderator

Previous Topic: How I execute package in function and procedure?
Next Topic: comma seperated input string to number
Goto Forum:
  


Current Time: Wed Dec 07 20:21:25 CST 2016

Total time taken to generate the page: 0.06034 seconds