how to do this GROUP BY in SQL*PLUS

How can I perform the following GROUP BY in SQL*PLUS:

For instance, I have a SALESMAN table with the following fields:

  SALESMAN - name of SALESMAN selling tickets   TICKET_NO - contains sequential numbers, just like with real tickets

What I would like to do is group by each SALESMAN with their corresponding series of TICKET_NO. The result would be like this:

SALESMAN      TICKET_NO_FROM     TICKET_NO_TO      COUNT
--------      ---------------    ------------      -----
JOHN          1                  50                50
              76                 100               25

RICK          51                 75                25
              176                200               25

TOM           151                175               25

WAYNE         76                 150               75

SELECT SALESMAN, MIN(TICKET_NO), MAX(TICKET_NO), COUNT(TICKET_NO)   FROM TICKETS
 GROUP
    BY SALESMAN However, this does not work because it fails to get two rows for JOHN and RICK. This is because it cannot sense that JOHN and RICK have *TWO* series of tickets.

I think the GROUP BY clause should be expanded so that it can detect a break in the sequential numbering of the tickets. HOW DO I DO THIS ???

If anyone has an alternative way around this GROUP BY, I'd be interested to try your method out.

Thanks. Received on Thu May 28 1992 - 00:47:06 CEST