comparing previous record with current record of a query 2

[LaurenM]

I'm trying to create a query that will show me when there is a difference of say 10% (the absolute amount really doesn't matter) between the previous record and the current record in a table.
Example:
Name |Score
John |80
John |85
John |100

The query should show me the records with the scores of 85 and 100. I saw a command that would do it, but I can't find it. Please help.

Lauren

 

[Thargy]

Lauren,

I think you may be alluding to the lag analytic function.

I have googled for "analytic functions lag" and got 2,970,000 hits, so there should be something in there to help you.

However, I should advise that your functional requirements aren't clear. Are you perhaps grouping by name, and then doing your calculations?

Please let us know if this satisfies your needs.

Regards

Tharg

Grinding away at things Oracular

 

[LaurenM]

Yes I am grouping by name, and then I need to compare each person's scores, if there is a significant increase or decrease, between one score and the previous one, then I need to take a look at what happened. I found the command previously but I forgot to bookmark it. The command was something like commandname fieldname (previous 1 and next1). I know its a SQL, but can't remember if it is an Oracle SQL command.

 

[taupirho]

Try this query

select name,
score,
100 *score/lag(score) over(partition by name order by name) -100 pct_diff
from your_table

 

[maswien]

or


select t1.name, t1.score
from myTable t0, myTable t1, myTable t2
where
((t1.score - t0.score) / t0.score > 0.2 and t0.name = t1.name
or (t2.score - t1.score) / t1.score > 0.2 and t1.name = t2.name)

 

[LaurenM]

Maswien, while that would work, the tables that I am using are actually four levels of inline views. I've solved the problem by using the lag/lead functions.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan