Re: SQL QUESTION: PLEASE HELP!!!!

jmboivin_at_cti.ulaval.ca (Jean-Marc Boivin) writes:

> I have a table wich could look like this:
 

> table x
> ticket_number number,
> ticket_status char(1)
 

> And the data's:
 

> ticket_number ticket_status
> ---------------------- --------------------
> 1
> 2
> 3
> 4 S
> 5
> 6
> 7 S
> 8
> 9
> 10
 

> and so...
 

> You understand that the ticket_status S is for sold.
> So, I want a SQL which will list all tickets not sold like this:
 

> first of group last of group
> -------------------- ---------------------
> 1 3
> 5 6
> 8 10
 

> Anybody know how to do this efficiently just with a SQL (no pl*sql).

Sorry for going over 80 columns...

I am assuming that X.Ticket_Number should be Not Null, and that the Ticket_Number values are all unique; ie. this is the Primary Key for the table X, or, at the very least, you'll do well to have a unique index on Ticket_Number.

I am Not assuming that the first Ticket_Number is necessarily 1 (or the last is 10) (also not assuming posative numbers nor integers), and am Not assuming that Ticket_Numbers are contiguous; if either of these (negative) assumptions are false, then the query becomes easier.

This looks a bit messy, but this is my best shot...

Select	A.Ticket_Number	"first of group"
	B.Ticket_Number	"last of group"
From	X	A,
	X	B
Where	A.Ticket_Status	Is	Null	And
	B.Ticket_Status	Is	Null	And
	(
	Exists (	Select	Null
			From	X	C
			Where	Ticket_Number	<	A.Ticket_Number	And
				Ticket_Status	Is Not	Null		And
				Not Exists (	Select	Null
						From	X
						Where	Ticket_Number	>	C.Ticket_Number	And
							Ticket_Number	<	A.Ticket_Number))	Or
	Not Exists (	Select	Null
			From	X
			Where	Ticket_Number	<	A.Ticket_Number)
	)	And
	(
	Exists (	Select	Null
			From	X	D
			Where	Ticket_Number	>	B.Ticket_Number	And
				Ticket_Status	Is Not	Null		And
				Not Exists (	Select	Null
						From	X
						Where	Ticket_Number	<	D.Ticket_Number	And
							Ticket_Number	>	B.Ticket_Number))	Or
	Not Exists (	Select	Null
			From	X
			Where	Ticket_Number	>	B.Ticket_Number)
	)	And
	Not Exists (	Select	Null
			From	X
			Where	Ticket_Number	>	A.Ticket_Number	And
				Ticket_Number	<	B.Ticket_Number	And
				Ticket_Status	Is Not	Null);

> Thank's!

You're welcome. Hope this helps.

> Jean-Marc Boivin
> Universite Laval
> Quebec city, Canada
> mail: jm_boivin_at_cti.ulaval.ca
> phone: 418-656-3632

Hank Robinson.
Oracle DBA,
University of Warwick. Received on Wed Jul 20 1994 - 13:28:35 CEST