RE: Tough SQL Problem

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 8 Feb 2006 14:01:33 -0500
Message-ID: <AA29A27627F842409E1D18FB19CDCF2706C01B59@AABO-EXCHANGE02.bos.il.pqe>

Heh....True. Jared has a valid point.  

To be clear, without another column that provides some order, there is NO WAY to tell what order the rows were inserted into that table.  

-Mark
 

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

"There are 10 types of people in the world: Those who understand binary, and those who don't."  

---

From: Jared Still [mailto:jkstill_at_gmail.com] Sent: Wednesday, February 08, 2006 1:59 PM To: Bobak, Mark
Cc: post.ethan_at_gmail.com; oracle-l
Subject: Re: Tough SQL Problem

Adding just a bit to Mark's reply, the fact that you see '1' 5 times consecutively does not mean that it was called 5 time consecutively.

Mark implied that, danced all around it and flirted a bit with it, but didn't come right out and say it. ;)

Jared

On 2/8/06, Bobak, Mark <Mark.Bobak_at_il.proquest.com > wrote:

        Hmm....just the one column? You need another column, by which you can

        establish order. There is no order inherent in a relational table. So,

        if you had another column, say, sequence_number, or a date_time_col or

        something, you could order by that, and relative to that, determine the

        largest consecutive occurances of '1'. (I'm thinking lag()/lead() here,

        off the top of my head.)         

	I would argue that without another column to order by, it's not
	possible, cause there's no way to determine the order of the

rows.         

        -Mark                  

	--
	Mark J. Bobak
	Senior Oracle Architect
	ProQuest Information & Learning
	
	"There are 10 types of people in the world:  Those who
understand
	binary, and those who don't." 
	
	-----Original Message-----
	From: oracle-l-bounce_at_freelists.org
	[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ethan Post 
	Sent: Wednesday, February 08, 2006 12:47 PM
	To: oracle-l
	Subject: Tough SQL Problem
	
	I have a log table that is logging various PLSQL calls. Assume
the table
	looks like this...
	
	PROCEDURE_ID
	============== 
	1
	2
	1
	1
	1
	1
	1
	2
	1
	1
	2
	1
	
	Looking at this I can see that the max times 1 was called
consecutivley
	is 5. I have no clue how I would do this in SQL without writing
a
	function of some sort to keep a count and return the max count.
Anyone 
	know if this can be done using and built in functions?
	
	- Ethan
	--
	http://www.freelists.org/webpage/oracle-l
	
	
	
	
	

--

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 08 2006 - 13:01:33 CST