Using the SELECT statement

[Netherbeast]

Im using this SELECT statement to retrieve this information:

SELECT
RPAD(P.PERSONID, 6) ID#,
LPAD(P.LAST, 9) LAST,
LPAD(P.FIRST, 9) FIRST,
LPAD(C.CONTACTTYPE, 9) CONTACT
FROM PERSON P, CONTACT C
WHERE P.PERSONID = C.PERSONID
ORDER BY LAST;

ID# LAST FIRST CONTACT
------ ---------- --------- ---------
1005 Nakahara Aaron EMAIL
1011 Shiratori Brent CELL
1011 Shiratori Brent EMAIL
1004 Takahashi Chad CELL
1004 Takahashi Chad EMAIL
1004 Takahashi Chad WORK
1006 Takahashi Shayne CELL
1006 Takahashi Shayne EMAIL

What do I need to change in the SELECT to make the data look like this:

ID# LAST FIRST CONTACT
------ ---------- --------- ---------
1005 Nakahara Aaron EMAIL
1011 Shiratori Brent CELL
EMAIL
1004 Takahashi Chad CELL
EMAIL
WORK
1006 Takahashi Shayne CELL
EMAIL

Basically, I dont want to have repeats of the ID#, Last, First.

Thanks in advance

 

[sem]

In sql*plus you may use BREAK command:

BREAK ON ID# ON Last ON First

I also think that you should order your results by these fields.

Regards, Dima

http://seminihin.narod.ru

 

[Netherbeast]

Thanks Dima

Where do I add the BREAK statements? I keep getting errors.

Thanks

 

[SantaMufasa]

Since it is 9:30 P.M. for Dima (and it might be after bedtime in Kyiv, Ukraine), I'll answer in Dima's behalf:
The SQL*Plus (not SQL) "BREAK" command must occur chronologically prior to execution of the SQL SELECT statement...Placing BREAK on the line just prior to the SELECT should be just fine.

If that is not working for you, please advise.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:32 (03Dec03) GMT, 12:32 (03Dec03) Mountain Time)

 

[Netherbeast]

Thanks to the both of you. You guys ROCK!!!