Home » SQL & PL/SQL » SQL & PL/SQL » Please help me to write a SQL (SQL )
Please help me to write a SQL [message #572370] Tue, 11 December 2012 04:26 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
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

Re: Please help me to write a SQL [message #572538 is a reply to message #572374] Thu, 13 December 2012 01:12 Go to previous messageGo to next message
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
Re: Please help me to write a SQL [message #572551 is a reply to message #572538] Thu, 13 December 2012 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
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
Re: Please help me to write a SQL [message #572721 is a reply to message #572551] Sun, 16 December 2012 12:41 Go to previous messageGo to next message
guesthyd
Messages: 12
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
Re: Please help me to write a SQL [message #572722 is a reply to message #572721] Sun, 16 December 2012 13:14 Go to previous message
Michel Cadot
Messages: 58954
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
Previous Topic: select queries
Next Topic: Bulk insert into file
Goto Forum:
  


Current Time: Mon Sep 01 20:13:18 CDT 2014

Total time taken to generate the page: 1.38781 seconds