OraFAQ Forum: SQL & PL/SQL » Please help me to write a SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Please help me to write a SQL (SQL )

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

Please help me to write a SQL [message #572370]

Tue, 11 December 2012 04:26

pvsatish.etl
Messages: 2
Registered: December 2012
Location: Banglore

Junior Member

Hi I have data like below

ITEM price day
-----------------
Item1 10 day1
Item1 20 day2
Item1 20 day3
Item1 20 day4
item1 30 day5
item1 10 day6
item1 40 day7
item1 40 day8

Requirement:
If price of item is changed on next day i need to capture that info else not required.
The item price on day2,3,4 is same so I need only day2 info.
The item price on day1 and 6 is same bat I need both because in beet 1 and 6 price got changed.

My out put should be like below

ITEM price day
-----------------
Item1 10 day1
Item1 20 day2
item1 30 day5
item1 10 day6
item1 40 day7

Can any one help me to write the SQL for above requirement.

Thanks in Advance ...........

[Updated on: Tue, 11 December 2012 04:37]

Report message to a moderator

Re: Please help me to write a SQL [message #572374 is a reply to message #572370]

Tue, 11 December 2012 04:40

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

Senior Member
Account Moderator

Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Have a look at LAG function.

Regards
Michel

Report message to a moderator

Re: Please help me to write a SQL [message #572538 is a reply to message #572374]

Thu, 13 December 2012 01:12

pvsatish.etl
Messages: 2
Registered: December 2012
Location: Banglore

Junior Member

Hi Michel,

Thanks for your replay. I am able to done by LAG fun...

In my next post i will fallow the format

Thanks,
Pvstaih

Report message to a moderator

Re: Please help me to write a SQL [message #572551 is a reply to message #572538]

Thu, 13 December 2012 02:15

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

Senior Member
Account Moderator

It is welcome, appreciated and fair you post the solution you found when you post a question.

Regards
Michel

Report message to a moderator

Re: Please help me to write a SQL [message #572721 is a reply to message #572551]

Sun, 16 December 2012 12:41

guesthyd
Messages: 8
Registered: April 2012

Junior Member

select a.item,a.day,a.price,a.previous_price
from (

select
item,price,day,lag(price,1) over (order by day) as previous_price
from item_price)a

where a.price<>a.previous_price
or a.previous_price is null

Report message to a moderator

Re: Please help me to write a SQL [message #572722 is a reply to message #572721]

Sun, 16 December 2012 13:14

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

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

And feedback to people that helped you , I am still waiting on your previous question.
http://www.orafaq.com/forum/mv/msg/180893/552788/102589/#msg_552788

Regards
Michel

Report message to a moderator

Previous Topic:	select queries
Next Topic:	Bulk insert into file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 24 17:04:20 CDT 2013

Total time taken to generate the page: 0.08155 seconds