Home » SQL & PL/SQL » SQL & PL/SQL » COMPARISON OF CURRENT RECORD WITH PREVIOUS OR NEXT RECORD
COMPARISON OF CURRENT RECORD WITH PREVIOUS OR NEXT RECORD [message #337788] Fri, 01 August 2008 03:59 Go to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
Hi Everybody,

I need to compare my sql records with their previous records. Actually requirement is suppose if previous record is same as current record then count should be one while if it is different then count should be 1.e.g.
fe count Actually fe count should be
fe1 1 1
fe1 1 0
fe1 1 0
fe2 1 1
fe2 1 0
fe3 1 1
fe4 1 1







[Updated on: Fri, 01 August 2008 04:02]

Report message to a moderator

Re: COMPARISON OF CURRENT RECORD WITH PREVIOUS OR NEXT RECORD [message #337794 is a reply to message #337788] Fri, 01 August 2008 04:06 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

You can make use of Analytic function (LEAD or LAG) to achieve your requirement...

http://www.psoug.org/reference/analytic_functions.html

Re: COMPARISON OF CURRENT RECORD WITH PREVIOUS OR NEXT RECORD [message #337797 is a reply to message #337788] Fri, 01 August 2008 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at LAG/LEAD functions.

Regards
Michel
Re: COMPARISON OF CURRENT RECORD WITH PREVIOUS OR NEXT RECORD [message #337803 is a reply to message #337797] Fri, 01 August 2008 04:30 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
thanks for suggestion,

but I had tried Lead/ Lag function but it degrades the sql performance as it complies of order by function and in my requirement I need to compare for 4 fields so correspondingly I need to apply order by on those 4 specific fields. Due to which sql takes a longer time to execute.

it will be great if you can suggest any other option.
Re: COMPARISON OF CURRENT RECORD WITH PREVIOUS OR NEXT RECORD [message #337808 is a reply to message #337803] Fri, 01 August 2008 04:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You'll alyways need to order the rows - without an ORDER BY, the rows come back in whatever order oracle thinks best.
Re: COMPARISON OF CURRENT RECORD WITH PREVIOUS OR NEXT RECORD [message #337810 is a reply to message #337794] Fri, 01 August 2008 04:51 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
thanks for the suggestion, but i had tried this option and it had degraded sql performance upto a great extent. As lead and alg involves order by and in my reuirement, I need to compare for 4 fields of the previous record.

In order to do that i will have to imply order by on those 4 fields which acts as a bubble sort and thus takes a longer duration of time when i have good number of records.

Please let me know if there is any other way to do this without effecting run time of sql much.

Thanks.
Re: COMPARISON OF CURRENT RECORD WITH PREVIOUS OR NEXT RECORD [message #337815 is a reply to message #337810] Fri, 01 August 2008 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it degrades the sql performance

Compare to what? To something that does not return what you want?
Do you have a solution that returns your result and don't use the function?
Post what you tried, what you have, your performances tests.

Regards
Michel
Re: COMPARISON OF CURRENT RECORD WITH PREVIOUS OR NEXT RECORD [message #338036 is a reply to message #337788] Sat, 02 August 2008 12:05 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Actually analytics are often the most efficient means of doing their kind of work Have a look at these:

Kevin Meade's OraFaq Blog

A Simple Example of Oracle Analytics: Running Totals

Analytics are very intelligent. In your case, all your analytic expressions will be the same, so analytics will do only one sort and then evaluate all the analytics as rows are output from the sort. Read the article.

Also, as Michel points out, if you are going to say something is slow, then you are obligated to define how you reached that conclusion, usually meaning you know some other way that is much faster.

The reality is (reality in this case meaning how analytics are used in the business of writing Oracle queries), that for 50% of the time (admittedly a guestimate based on my experiences), analytics are actually free, they have almost no cost. Read the article and see if you can tell us why this is so.

Kevin
Previous Topic: How to get a particular column
Next Topic: Pivot hints?
Goto Forum:
  


Current Time: Sun Dec 04 14:37:48 CST 2016

Total time taken to generate the page: 0.10823 seconds