Re: SQL QUESTION: PLEASE HELP!!!!

In article <jmboivin.16.000E7719_at_cti.ulaval.ca> 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)
>

[ deleted ]

>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).
>
>

hello jean-marc,

I have a solution with 2 views (one for the first_of_group, and one for the last_of_group):

CREATE VIEW first_of (nr, ticket_number, ticket_status) AS

SELECT	ROWNUM, ticket_number, ticket_status
FROM	x
WHERE	(ticket_number, NVL(ticket_status,'N')) in
(
	SELECT	ticket_number, NVL(ticket_status,'N')
	FROM	x
	MINUS
	SELECT	ticket_number +	1,nvl(ticket_status,'N')
	FROM	x

CREATE VIEW last_of (nr, ticket_number, ticket_status) AS

SELECT	ROWNUM, ticket_number, ticket_status
FROM	x
WHERE	(ticket_number, NVL(ticket_status,'N')) in
(
	SELECT	ticket_number, NVL(ticket_status,'N')
	FROM	x
	MINUS
	SELECT	ticket_number - 1,nvl(ticket_status,'N')
	FROM	x

(Note the small difference ticket_number +/- 1 after the minus !!)

and now you can select the tickets not sold:

SELECT	first_of.ticket_number first_of , last_of.ticket_number last_of
FROM	first_of, last_of
WHERE	first_of.ticket_status	IS NULL
AND	last_of.ticket_status	IS NULL
AND	first_of.nr		= last_of.nr
ORDER	BY first_of.nr

I did not test this with large amounts of data - hope this helps anyway! Gio Received on Thu Aug 04 1994 - 10:29:07 CEST